Oracle查询表的名字和comments
select a.table_name,b.comments from user_tables a,ALL_TAB_COMMENTS b where a.table_name=b.table_name
查询字段的名字和comments
select distinct a.COLUMN_NAME,b.comments from user_tab_columns a,user_col_comments b where a.COLUMN_NAME=b.column_name and a.TABLE_NAME=b.table_name and a.table_name=upper('A_COUNT');
创建一个目录
create directory exp_dir as '/home/oracle';
赋权限(需要sysdb用户)
grant create any directory to scott;
导出表和表字段注释的存储过程
create or replace procedure export_tableInfo as
fhandle utl_file.file_type;
begin
fhandle := utl_file.fopen('EXP_DIR', 'example.html', 'w',32767);
utl_file.put_line(fhandle ,'<!DOCTYPE HTML><html><head><meta charset="utf-8"><head><body>');
for t in (select a.table_name,b.comments from user_tables a,ALL_TAB_COMMENTS b where a.table_name=b.table_name and a.table_name not like 'T_BME%' order by a.table_name) loop
--dbms_output.put_line(t.table_name||','||t.comments);
utl_file.put_line(fhandle ,'<p>'||t.table_name||'('||t.comments||')</p>');
utl_file.put_line(fhandle ,'<table border="1">');
for c in (select distinct a.column_name,a.data_type,a.data_length,b.comments from user_tab_columns a,user_col_comments b where a.table_name=b.table_name and a.table_name=t.table_name and a.column_name=b.column_name) loop
--column_name,data_type ,data_length,data_precision,data_scale
--dbms_output.put_line(c.column_name||','||c.data_type||','||c.data_length||','||c.comments);
utl_file.put_line(fhandle ,'<tr><td>'||c.column_name||'</td><td>'||c.data_type||'</td><td>'||c.data_length||'</td><td>'||c.comments||'</tr>');
end loop;
utl_file.put_line(fhandle ,'</table>');
end loop;
utl_file.put_line(fhandle ,'</body></html>');
utl_file.fclose(fhandle);
end;