Oracle数据库表字段注释补充脚本

--创建临时表 存所有字段的注释
create table col_comments_tmp0 (owner varchar2(50),table_name varchar2(50),column_name varchar2(50),comments varchar2(2000),origin_con_id varchar2(50));
insert into col_comments_tmp0 select * from dba_col_comments where owner='IFM' AND TABLE_NAME LIKE 'T%';
commit;

--创建临时表 存注释为空的数据
create table col_comments_tmp1 (owner varchar2(50),table_name varchar2(50),column_name varchar2(50),comments varchar2(255),origin_con_id varchar2(50));
insert into col_comments_tmp1 select * from dba_col_comments where owner='IFM' AND TABLE_NAME LIKE 'T%'  AND COMMENTS IS NULL ;
commit;

--创建临时表  存储注释为空的数据的每个条数
create table col_comments_tmp2 (column_name varchar2(50),count varchar2(50));
insert into col_comments_tmp2 select column_name,count(column_name) from dba_col_comments where owner='IFM' AND TABLE_NAME LIKE 'T%' AND COMMENTS IS NULL group by column_name order by count(column_name) desc;
commit;

--创建临时表 存已有注释出现次数最多的数据
create table col_comments_tmp3 (owner varchar2(50),table_name varchar2(50),column_name varchar2(50),comments varchar2(2000));

--创建存储过程 逐条执行空注释的数据
create or replace procedure loop_through_table as
   begin
    for record in (select *from col_comments_tmp2) loop
    insert into col_comments_tmp3
    select owner,table_name,column_name,
        (select a.comments
          from (select comments, count(comments) as count
              from col_comments_tmp0
              where owner = 'IFM'
              AND TABLE_NAME LIKE 'T%'
              AND column_name = record.column_name
              group by comments
              order by count desc) a
          where rownum = 1)
      from col_comments_tmp1
      where column_name = record.column_name;
    commit;
   end loop;
end loop_through_table;

--命令行窗口执行存储过程
set serveroutput on;
exec loop_through_table;

--拼接comment语句
select 'comment on column '||owner||'.'||table_name||'.'||column_name||' is '''||comments||''';' from col_comments_tmp3 where owner='IFM' AND TABLE_NAME LIKE 'T%' AND COMMENTS IS NOT NULL;