oracle 导出数据字典

怼烎@ 2021-06-26 16:06 499阅读 0赞

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

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