Output table/row comments (Oracle 8+)

Language: English
Programming Language: SQL
Published by: hwaite [not registered]
Last Update: 5/15/2006
Views: 3379

Description

Outputs a description of all tables with a given owner. Will list any comments associated with the selected table/column. This is an Oracle-specific stored-proc.

Code

Display line numbers

1 -- This procedure outputs the bmf syntax of a table 2 3 CREATE OR REPLACE PROCEDURE output_docs(p_owner IN VARCHAR2) AS BEGIN 4 DECLARE 5 CURSOR tbl_cursor IS 6 SELECT all_tables.table_name name, 7 all_tab_comments.comments description 8 FROM all_tables, all_tab_comments 9 WHERE all_tables.owner = p_owner AND 10 all_tables.table_name = all_tab_comments.table_name (+) 11 UNION 12 SELECT all_views.view_name name, 13 all_tab_comments.comments description 14 FROM all_views, all_tab_comments 15 WHERE all_views.owner = p_owner AND 16 all_views.view_name = all_tab_comments.table_name (+) 17 ORDER BY 1; 18 tbl_cursor_val tbl_cursor%rowtype; 19 BEGIN 20 dbms_output.put_line('Comment Listing'); 21 dbms_output.put_line('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'); 22 FOR tbl_cursor_val IN tbl_cursor LOOP 23 dbms_output.put_line(tbl_cursor_val.name); 24 dbms_output.put_line(tbl_cursor_val.description); 25 dbms_output.put_line('-----'); 26 dbms_output.new_line; 27 DECLARE 28 CURSOR tbl_col_cursor IS 29 SELECT all_tab_columns.column_name name, DECODE ( 30 all_tab_columns.data_type, 31 'INTEGER', 'int', 32 'NUMBER', DECODE ( 33 all_tab_columns.data_scale, 0, 34 DECODE (all_tab_columns.data_precision, 1, 'boolean', 'int'), null, 35 'int', 'float' 36 ), 'DATE', 'java.sql.Date', 37 --'VARCHAR2', 'string', 38 --'VARCHAR', 'string', 39 --'CHAR', 'char', 40 'LONG RAW', 'binary', 41 'String' 42 ) datatype, 43 all_col_comments.comments description 44 FROM all_tab_columns, all_col_comments 45 WHERE all_tab_columns.table_name = tbl_cursor_val.name AND 46 all_tab_columns.table_name = all_col_comments.table_name AND 47 all_tab_columns.column_name = all_col_comments.column_name 48 ORDER BY all_tab_columns.column_id, all_tab_columns.column_name; 49 tbl_col_cursor_val tbl_col_cursor%rowtype; 50 BEGIN 51 FOR tbl_col_cursor_val IN tbl_col_cursor LOOP 52 dbms_output.put_line(tbl_col_cursor_val.name); 53 dbms_output.put_line(tbl_col_cursor_val.description); 54 END LOOP; 55 dbms_output.put_line('+++++++++++++++++++++++++++++++++'); 56 END; 57 END LOOP; 58 END; 59 END; 60 / 61 62 set serveroutput on; 63 begin dbms_output.enable (10000000); end; -- prevent buffer overflow 64 / 65 66 set linesize 250; 67 68 spool table_comments.txt; 69 70 execute output_docs('my_user'); 71 72 spool off; 73 74 quit; 75

Output table/row comments (Oracle 8+)

Description

Code

No comments avaiable

Add a comment