Title: Report of Database Tables, Indexes, and Constraints

Author: Saurabh Banerjee, a consultant for Computer
      People Inc., in Glenolden, Pennsylvania.

      This script creates a report of database tables,indexes and constraints by querying
      ORACLE data dictionary tables. Run this script in SQL*Plus. A temporary table
      TBL_METADATA is used for reporting. This script reports only on user tables. To report on
      tables belonging to other users replace "USER_" by "ALL_" and then run the script. To
      report on specific tables modify the main cursor. The results are stored into a text file
      REPORT.LST

      The script has been tested using : Oracle7 Server Release 7.3.3.6.1 - Production Release
      PL/SQL Release 2.3.3.6.0 - Production

      /********************************************************************************************
      PURPOSE: This script creates a report of database tables,indexes and constraints
      by querying ORACLE data dictionary tables. Run this script in SQL*Plus.
      A temporary table TBL_METADATA is used for reporting. This script reports only on user tables.
      To report on tables belonging to other users replace "USER_" by "ALL_" and then run the script.
      To report on specific tables modify the main cursor. The results are stored into a text file REPORT.LST

      The script has been tested using :
      Oracle7 Server Release 7.3.3.6.1 - Production Release
      PL/SQL Release 2.3.3.6.0 - Production

      AUTHOR : Saurabh Banerjee
      DATE   : 23-MAR-99
      ********************************************************************************************/

      SET HEAD OFF
      SET FEEDBACK OFF
      SET TERM OFF

      CREATE TABLE TBL_METADATA
      (
      SEQNO  NUMBER,
      DATA VARCHAR2(500)
      ) STORAGE ( INITIAL 1M NEXT 1M )
      /

      DECLARE

      --MAIN CURSOR
      CURSOR C_TABLES IS
      SELECT TABLE_NAME
      FROM USER_TABLES
      order by table_name;

      v_table_name    varchar2(30);

      CURSOR c_index(p_table_name IN VARCHAR2) IS
      SELECT INDEX_NAME,
             COLUMN_NAME,
             COLUMN_POSITION
      FROM USER_IND_COLUMNS
      WHERE TABLE_NAME = p_table_name
      order by index_name,COLUMN_position;

      CURSOR c_CONSTRAINTS(p_table_name IN VARCHAR2) IS
      SELECT cons.CONSTRAINT_NAME,
             DECODE(cons.CONSTRAINT_TYPE,'C','CHECK','P','PRIMARY KEY','R','FOREIGN KEY') CONSTRAINT_TYPE,
             conscol.column_name,
             cons.search_condition,
             cons.r_constraint_name
      FROM user_cons_columns conscol,
           USER_CONSTRAINTS cons
      WHERE cons.constraint_name = conscol.constraint_name
      and cons.TABLE_NAME = p_table_name
      order by cons.CONSTRAINT_name,conscol.position;

      CURSOR c_r_CONSTRAINTS(p_constraint_name IN VARCHAR2) IS
      SELECT cons.CONSTRAINT_NAME,
             CONS.TABLE_NAME,
             DECODE(cons.CONSTRAINT_TYPE,'C','CHECK','P','PRIMARY KEY','R','FOREIGN KEY') CONSTRAINT_TYPE,
             conscol.column_name,
             cons.search_condition
      FROM user_cons_columns conscol,
           USER_CONSTRAINTS cons
      WHERE cons.constraint_name = conscol.constraint_name
      and cons.constraint_NAME = p_constraint_name
      order by conscol.position;


      v_seqno TBL_METADATA.SEQNO%TYPE;
      v_data  TBL_METADATA.DATA%TYPE;

      procedure insert_row is

      begin
        v_seqno := v_seqno + 1;
        INSERT INTO TBL_METADATA values (v_seqno,v_data);
      end;

      BEGIN
      delete from tbl_metadata;
      open c_tables;
      loop
        fetch c_tables into v_table_name;
        exit when c_tables%NOTFOUND;
        v_data := 'PROMPT ** table ' ||v_table_name || ' **';
        insert_row;

        v_data := 'DESC ' ||v_table_name ;
        insert_row;

      --GET INDEXES
        for c_ind in c_index(v_table_name) loop
          v_data := 'PROMPT --INDEX: '||c_ind.index_name || ' ' || c_ind.column_name ;
          insert_row;
        end loop;

      --GET CONSTRAINTS
        for c_con in c_constraints(v_table_name) loop
          v_data := 'PROMPT --CONSTRAINT: '||c_con.constraint_name || ' ' ||c_con.column_name
                   || ' ' || c_con.constraint_type || ' ' || RTRIM(c_con.search_condition);
          insert_row;
          if c_con.r_constraint_name is not null then
            for c_rcon in c_r_constraints(c_con.constraint_name) loop
              v_data := 'PROMPT ----REFERENCE CONSTRAINT: '||c_rcon.constraint_name|| ' '
                           ||c_rcon.TABLE_name || ' ' ||c_rcon.column_name || ' ' ||
                               RTRIM(c_rcon.search_condition);
              insert_row;
            end loop;
          END IF;
        end loop;

        v_data := 'PROMPT *****************************************************************';
        insert_row;

      end loop;
      close c_tables;

      commit;
      END;
      /

      --EXEC GET_METADATA;
      SPOOL 1.LST
      SELECT DATA FROM TBL_METADATA ORDER BY SEQNO;
      SPOOL OFF
      SPOOL REPORT.LST
      @1.LST
      SPOOL OFF
      DROP TABLE TBL_METADATA;
      SET FEEDBACK ON
      SET TERM ON
      ED REPORT.LST