oracle 导出数据字典

最新推荐文章于 2026-05-08 21:40:29 发布

转载最新推荐文章于 2026-05-08 21:40:29 发布 · 2.1k 阅读

Oracle数据库专栏收录该内容

162 篇文章

订阅专栏

博客主要介绍了两方面内容，一是查看当前用户下表名及其备注，二是查询数据字典，这些操作与数据库相关。

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

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