一、查看当前用户下表名,及表名的备注
select * from user_tab_comments where table_name like 'T_ONLINE%'
二、查询数据字典
-- 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