Search This Blog

Total Pageviews

Saturday 28 January 2012

Table Comment info user wise

Table Comment info user wise

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 '%ANUJ%' -- omit Anuj Table 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;

Oracle DBA

anuj blog Archive