SQL Script Database Documentor

Updated : 28-March-2002
Version : 2.0

Description

This displays table and column comments. Useful for a quick summary of databases to give to users who wish to develop reports.

Parameters

&1 - Table Owner

SQL Source

set serveroutput on size 1000000
spool docdb.txt
DECLARE
l_unixdb_owner VARCHAR2(20) := UPPER('&1');

CURSOR get_tc IS
        SELECT * FROM dba_tab_comments
        WHERE comments IS NOT NULL
        AND   owner=l_unixdb_owner
        AND   table_name not like '%JN' -- omit Designer journal tables
        ORDER BY table_name;

CURSOR get_cc (p_owner dba_tables.owner%TYPE
                ,p_table_name dba_tables.table_name%TYPE) IS
        SELECT dcc.comments
             , dtc.column_name
             , DECODE(dtc.nullable,'Y','(Optional)','N','(Mandatory)') nullable
        FROM dba_col_comments dcc, dba_tab_columns dtc
        WHERE dcc.owner = dtc.owner
        AND   dcc.table_name = dtc.table_name
        AND   dcc.column_name = dtc.column_name
        AND dcc.owner=p_owner AND dcc.table_name= p_table_name
        AND dcc.comments IS NOT NULL
        ORDER BY dtc.column_name
        ;
BEGIN
FOR tab_rec IN get_tc LOOP
   dbms_output.put_line('.');
   dbms_output.put_line('------------------- Start Of '
          ||tab_rec.table_type
          ||' '
          ||tab_rec.table_name
          || ' ---------------------');
   dbms_output.put_line('.');
   dbms_output.put_line(tab_Rec.table_type||' Description');
   dbms_output.put_line('-----------------');
   dbms_output.put_line(tab_rec.comments);
   FOR col_rec IN get_cc (tab_rec.owner, tab_rec.table_name) LOOP

      IF get_cc%ROWCOUNT = 1 THEN
         dbms_output.put_line('.');
         dbms_output.put_line('Column Descriptions');
         dbms_output.put_line('-------------------');
      END IF;

      dbms_output.put_line (col_rec.column_name  
                                ||' '||col_rec.nullable);
      dbms_output.put_line (col_rec.comments);

   END LOOP;

   dbms_output.put_line('.');
   dbms_output.put_line('------------------- End Of Object ---------------------'
);

END LOOP;

END;
/

Return to Index of SQL Scripts

Home | Company Profile | Services | Contact Us | SQL scripts and tips | Quiz Legal