oracle 导出数据字典

一、查看当前用户下表名，及表名的备注

SELECT * FROM USER_TAB_COMMENTS WHERE TABLE_NAME LIKE 'WEB_PLY_BASE%';

SELECT * FROM DBA_TAB_COMMENTS WHERE TABLE_NAME LIKE 'WEB_PLY_BASE%';

二、查询数据库某表字段

SELECT WM_CONCAT(COLUMN_NAME)
FROM USER_TAB_COLS@LINK_CORE
WHERE TABLE_NAME = UPPER('T_WEB_PLY_BASE')
ORDER BY COLUMN_ID;

SELECT WM_CONCAT(COLUMN_NAME)
FROM DBA_TAB_COLS@LINK_CORE
WHERE TABLE_NAME = UPPER('WEB_PLY_BASE')
AND OWNER = 'ZSSYS'
ORDER BY COLUMN_ID;

三、查询数据字典

-- 1. 查询某表的数据字典
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';

-- 2. 具有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;

-- 3.一般用户导出该用户下的数据字典
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;

-----使用下面语句从all_constraints视图中查看某表上的约束：
SELECT constraint_name, table_name, r_owner, r_constraint_name FROM all_constraints
WHERE table_name = 'TBL_ORGAN_SALES' and owner = 'ZSWX';