oracle使用sql生成表结构文档

oracle使用sql生成表结构文档

背景

客户要求数据资产盘点,需要提供相关表字段的说明文档，指定具体格式。手动是不可能手动的，

使用SQL实现。

要求

实现

生成脚本sql

查询所有非临时表

SELECT table_name
FROM all_tables
WHERE temporary = 'N';

--默认没有临时表
SELECT table_name
FROM user_tables;

全部查询sql

SELECT cols.column_id, '' as "四级分类名称", max(ut.comments) as "逻辑数据实体",
       cols.table_name as "逻辑数据实体", MAX(comm.comments) AS "数据项名称",
       cols.column_name as "数据项名称", MAX(comm.comments) AS "数据项业务定义",
       (case
         when cols.data_type = 'NUMBER' THEN
          '数值类'
         when cols.data_type = 'CHAR' then
          '标志类'
         else
          '文本类'
       end) as "数据项类型", '行业标准' as "参考标准"
  FROM user_tab_columns cols
  left JOIN all_col_comments comm
    ON cols.column_name = comm.column_name
  left join user_tab_comments ut
    on lower(cols.table_name) = lower(ut.table_name)
   AND UT.comments IS NOT NULL
 WHERE cols.table_name IN
       (SELECT upper(table_name) FROM user_tables)
 group by cols.table_name, cols.column_id, cols.column_name, cols.data_type,
          cols.data_length
 ORDER BY cols.table_name, cols.column_id;

结语

学习要勤奋，干活策略上要偷懒。