Oracle 使用SQL语句生成数据库数据字典文档

select decode(b.COLUMN_ID,1,c.comments,'') as 表中文名,
       a.table_name  as 表名,
       b.COLUMN_ID   as cid,
       b.COLUMN_NAME as 字段,
       decode(b.DATA_TYPE,
                'BLOB','BLOB',
                'CHAR','CHAR(' || b.DATA_LENGTH ||')',
                'CLOB','CLOB',
                'DATE','DATE',
                'FLOAT','FLOAT',
                'NUMBER',case when(b.DATA_LENGTH=22 and b.DATA_SCALE=0) then 'INTEGER' else 'NUMBER(' ||b.DATA_LENGTH || ','|| b.DATA_SCALE || ')' end,
                'RAW','RAW(' || b.DATA_LENGTH || ')',
                'TIMESTAMP(6)','TIMESTAMP',
                'VARCHAR','VARCHAR(' || b.DATA_LENGTH ||')',
                'VARCHAR2','VARCHAR2(' || b.DATA_LENGTH ||')',
                b.DATA_TYPE) as 数据类型,
       b.NULLABLE as 可空,
       decode(b.NULLABLE,'Y','N','Y') as 是否必填,
       b.DATA_DEFAULT as 默认,
       d.comments    as 备注
  from user_tables a
  join user_tab_columns b on a.table_name = b.TABLE_NAME
  left outer join user_tab_comments c on a.table_name = c.table_name
  left outer join user_col_comments d on (b.TABLE_NAME = d.table_name and
                                         b.COLUMN_NAME = d.column_name)
  order by a.table_name,b.COLUMN_ID