oracle 导出字典表,oracle 导出数据字典

select * from user_tab_comments where table_name like ‘T_ONLINE%‘

二、查询数据字典

1. 具有dba权限用户导出数据字典

SELECT  A.TABLE_NAME AS "表名",A.COLUMN_NAME AS "字段名",

DECODE(A.CHAR_LENGTH,0,DECODE(A.DATA_SCALE,NULL,A.DATA_TYPE,A.DATA_TYPE||‘(‘||A.DATA_PRECISION||‘,‘||A.DATA_SCALE||‘)‘),

A.DATA_TYPE||‘(‘||A.CHAR_LENGTH||‘)‘) as "字段类型",A.DATA_DEFAULT AS "默认值",

A.NULLABLE AS "能否为空",B.comments AS "备注"

FROM sys.all_tab_columns A,sys.DBA_COL_COMMENTS B

WHERE A.owner=B.owner AND A.table_name=B.table_name  AND A.COLUMN_NAME=B.COLUMN_NAME AND A.owner=‘guoqiang‘  AND

A.TABLE_NAME IN (

‘TB_SUBJECT‘, ‘TB_SUBJECT_BALANCE‘, ‘TB_VOUCHER‘, ‘TB_VOUCHER_DETAILS‘,

‘TB_CUSTOMER‘, ‘TB_VOUCHER_CLASSIFY_MODE‘, ‘TB_VOUCHER_TYPE‘, ‘TB_ASSET‘,

‘TB_ASSET_CATALOG‘, ‘TB_M_DM_ASSETS_LIABI_RPT‘, ‘TB_M_DM_PROFIT_RPT‘,

‘TB_M_DM_REVENUE_RPT‘, ‘TB_M_DM_COST_RPT‘

)

ORDER BY A.TABLE_NAME

2.一般用户导出该用户下的数据字典

SELECT  A.TABLE_NAME AS "表名",A.COLUMN_NAME AS "字段名",

DECODE(A.CHAR_LENGTH,0,DECODE(A.DATA_SCALE,NULL,A.DATA_TYPE,A.DATA_TYPE||‘(‘||A.DATA_PRECISION||‘,‘||A.DATA_SCALE||‘)‘),

A.DATA_TYPE||‘(‘||A.CHAR_LENGTH||‘)‘) as "字段类型",A.DATA_DEFAULT AS "默认值",

A.NULLABLE AS "能否为空",B.comments AS "备注"

FROM sys.user_tab_columns A,sys.user_col_comments B

WHERE A.table_name=B.table_name  AND A.COLUMN_NAME=B.COLUMN_NAME AND

A.TABLE_NAME IN (

‘TB_SUBJECT‘, ‘TB_SUBJECT_BALANCE‘, ‘TB_VOUCHER‘, ‘TB_VOUCHER_DETAILS‘,

‘TB_CUSTOMER‘, ‘TB_VOUCHER_CLASSIFY_MODE‘, ‘TB_VOUCHER_TYPE‘, ‘TB_ASSET‘,

‘TB_ASSET_CATALOG‘, ‘TB_M_DM_ASSETS_LIABI_RPT‘, ‘TB_M_DM_PROFIT_RPT‘,

‘TB_M_DM_REVENUE_RPT‘, ‘TB_M_DM_COST_RPT‘

)

ORDER BY A.TABLE_NAME

oracle 导出数据字典

标签：revenue   column   ||   line   tail   null   font   tom   and

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉 本文系统来源：https://www.cnblogs.com/52KT9/p/11725033.html