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 "数据项业务定义",(casewhen cols.data_type = 'NUMBER' THEN'数值类'when cols.data_type = 'CHAR' then'标志类'else'文本类'end) as "数据项类型", '行业标准' as "参考标准"FROM user_tab_columns colsleft JOIN all_col_comments commON cols.column_name = comm.column_nameleft join user_tab_comments uton lower(cols.table_name) = lower(ut.table_name)AND UT.comments IS NOT NULLWHERE 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_lengthORDER BY cols.table_name, cols.column_id;
结语
学习要勤奋，干活策略上要偷懒。