Oracle导出数据字典脚本
1.导出全部表
1 SELECT COL.TABLE_NAME AS "表名", 2 TT.COMMENTS AS "表描述", 3 COL.COLUMN_NAME AS "字段名", 4 DECODE(COL.CHAR_LENGTH,0,DECODE(COL.DATA_SCALE,NULL,COL.DATA_TYPE,COL.DATA_TYPE||'('||COL.DATA_PRECISION||','||COL.DATA_SCALE||')'), 5 COL.DATA_TYPE||'('||COL.CHAR_LENGTH||')') AS "字段类型", 6 COL.DATA_DEFAULT AS "默认值", 7 COL.NULLABLE AS "是否可为空", 8 CCOM.COMMENTS AS "字段描述" 9 FROM USER_TAB_COLUMNS COL, 10 USER_COL_COMMENTS CCOM, 11 (SELECT AA.TABLE_NAME, 12 AA.INDEX_NAME, 13 AA.COLUMN_NAME, 14 AA.COLUMN_POSITION 15 FROM USER_IND_COLUMNS AA, USER_CONSTRAINTS BB 16 WHERE BB.CONSTRAINT_TYPE = 'P' 17 AND AA.TABLE_NAME = BB.TABLE_NAME 18 AND AA.INDEX_NAME = BB.CONSTRAINT_NAME 19 ) PKCOL, 20 USER_TAB_COMMENTS TT 21 WHERE COL.TABLE_NAME = CCOM.TABLE_NAME 22 AND COL.COLUMN_NAME = CCOM.COLUMN_NAME 23 AND COL.TABLE_NAME = TT.TABLE_NAME(+) 24 AND COL.COLUMN_NAME = PKCOL.COLUMN_NAME(+) 25 AND COL.TABLE_NAME = PKCOL.TABLE_NAME(+) 26 ORDER BY COL.TABLE_NAME,col.column_id
2.导出指定表数据字典
1 SELECT 2 A.TABLE_NAME AS "表名", 3 A.COLUMN_NAME AS "字段名", 4 DECODE(A.CHAR_LENGTH,0,DECODE(A.DATA_SCALE,NULL,A.DATA_TYPE,A.DATA_TYPE||'('||A.DATA_PRECISION||','||A.DATA_SCALE||')'), 5 A.DATA_TYPE||'('||A.CHAR_LENGTH||')') as "字段类型", 6 A.DATA_DEFAULT AS "默认值", 7 A.NULLABLE AS "能否为空", 8 B.comments AS "备注" 9 FROM sys.user_tab_columns A,sys.user_col_comments B 10 WHERE A.table_name=B.table_name AND A.COLUMN_NAME=B.COLUMN_NAME AND 11 A.TABLE_NAME IN ( 12 'TB_SUBJECT', 'TB_SUBJECT_BALANCE', 'TB_VOUCHER', 'TB_VOUCHER_DETAILS', 13 'TB_CUSTOMER', 'TB_VOUCHER_CLASSIFY_MODE', 'TB_VOUCHER_TYPE', 'TB_ASSET', 14 'TB_ASSET_CATALOG', 'TB_M_DM_ASSETS_LIABI_RPT', 'TB_M_DM_PROFIT_RPT', 15 'TB_M_DM_REVENUE_RPT', 'TB_M_DM_COST_RPT' 16 ) 17 ORDER BY A.TABLE_NAME
参考链接
https://blog.csdn.net/tanguang_honesty/article/details/19482579