oracle 如何导出数据字典

    xiaoxiao2021-03-25  50

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

      --  具有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

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

    本文来自博客,转载请标明出处:http://blog.csdn.net/guoqiang1983/archive/2008/05/21/2466742.aspx

    转载请注明原文地址: https://ju.6miu.com/read-39879.html

    最新回复(0)