Oracle中使用SQL获取所有表名,表注释,字段注释

mtain 2017年12月13日 701次浏览

SQL如下:

-- 查询表名,表注释,字段注释
select ATC.OWNER,
       atC.TABLE_NAME,
       utc.comments,
       ATC.COLUMN_NAME,
       ATC.DATA_TYPE,
       ATC.DATA_LENGTH,
       ATC.NULLABLE,
       ucc.comments
  from (select ATC.OWNER,
               atC.TABLE_NAME,
               ATC.COLUMN_NAME,
               ATC.DATA_TYPE,
               ATC.DATA_LENGTH,
               ATC.NULLABLE
          from all_tab_columns ATC
         where ATC.owner in ('USER_1')) atc
  left outer join user_col_comments ucc on atc.table_name = ucc.table_name
                                       and atc.column_name =
                                           ucc.column_name
  left outer join user_tab_comments utc on atc.table_name = utc.table_name
 order by atc.table_name, atc.column_name;



-- 表注释SQL
select 'comment on table ' || atC.TABLE_NAME || ' is -' ||
                utc.comments || '-;'
  from (select ATC.OWNER,
               atC.TABLE_NAME,
               ATC.COLUMN_NAME,
               ATC.DATA_TYPE,
               ATC.DATA_LENGTH,
               ATC.NULLABLE
          from all_tab_columns ATC
         where ATC.owner in ('USER_1')) atc
  left outer join user_tab_comments utc on atc.table_name = utc.table_name
 order by atc.table_name, atc.column_name;



-- 字段注释
select 'comment on column '||atC.TABLE_NAME||'.'||ATC.COLUMN_NAME||' is -'||ucc.comments||'-;'
  from (select ATC.OWNER,
               atC.TABLE_NAME,
               ATC.COLUMN_NAME,
               ATC.DATA_TYPE,
               ATC.DATA_LENGTH,
               ATC.NULLABLE
          from all_tab_columns ATC
         where ATC.owner in ('USER_1')) atc
  left outer join user_col_comments ucc on atc.table_name = ucc.table_name
                                       and atc.column_name =
                                           ucc.column_name
  left outer join user_tab_comments utc on atc.table_name = utc.table_name
 order by atc.table_name, atc.column_name;