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