数据字典生成SQL语句

–将当前用户下所有表信息生成在一张表中，可以导出excel后拆分处理
SELECT
A.TABLE_NAME AS “表名”,
A.COLUMN_NAME AS “字段名称”,
B.COMMENTS AS “字段描述”,
A.DATA_TYPE as 字段类型,
REPLACE(DECODE(A.CHAR_LENGTH,0,DECODE(A.DATA_SCALE,NULL,A.DATA_TYPE,A.DATA_PRECISION||’,’||A.DATA_SCALE),A.CHAR_LENGTH),’,0’,’’) 字段长度,
REPLACE(REPLACE(A.NULLABLE,‘Y’,‘√’),‘N’,’’) AS “能否为空”,
A.DATA_DEFAULT AS “默认值”
FROM
USER_TAB_COLUMNS A,
USER_COL_COMMENTS B
WHERE
A.TABLE_NAME= B.TABLE_NAME
AND
A.COLUMN_NAME=B.COLUMN_NAME
AND
A.TABLE_NAME IN ( SELECT TABLE_NAME FROM USER_TABLES)–只是生成表数据，没有此条会将视图和表一起查出来
ORDER BY A.TABLE_NAME