Oracle Schema comment report

Oracle comment report
Oracle comment


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;
/



@comment SYSTEM




------------------- End Of Object ---------------------
.
------------------- Start Of TABLE REPCAT$_TEMPLATE_TYPES ---------------------
.
TABLE Description
-----------------
Internal table for maintaining types of templates.
.
Column Descriptions
-------------------
FLAGS (Optional)
Bitmap flags controlling each type of template.
SPARE1 (Optional)
Reserved for future expansion.
TEMPLATE_DESCRIPTION (Optional)
Description of the template type.
TEMPLATE_TYPE_ID (Mandatory)
Internal primary key of the template types table.
.
------------------- End Of Object ---------------------