Oracle数据字典的导出

-- 查询某表的数据字典
 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 "字段类型1",A.DATA_TYPE AS "字段类型",A.DATA_PRECISION AS "有效位",A.DATA_SCALE AS "精度值",
A.CHAR_LENGTH AS "字段长度",A.NULLABLE AS "能否为空"
 FROM sys.user_tab_columns A where A.table_name = 'TAM_ADDRESS'

 
--  具有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


--一般用户导出该用户下的数据字典
 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',
'T


本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/qingwaxw1980/archive/2009/12/11/4985238.aspx

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值