oracle 导出表结构，带主键

jxw201615060094

已于 2024-04-06 15:39:55 修改

阅读量1.2k

点赞数

文章标签：数据库

于 2022-04-16 19:16:13 首次发布

本文链接：https://blog.youkuaiyun.com/w201615060094/article/details/124218641

版权

user 用户导出

SELECT    a.table_name
        , c.comments
        , a.column_name
        , b.comments
        , a.data_type
        , a.data_length
        , a.data_precision
        , a.data_scale
        , d.position
     FROM user_tab_cols                 a
LEFT JOIN user_col_comments             b
       ON a.table_name  = b.table_name
      AND a.column_name = b.column_name
LEFT JOIN user_tab_comments             c
       ON c.table_name = b.table_name
LEFT JOIN
          (SELECT ucc.table_name
                  , ucc.column_name
                  , ucc.position
               FROM user_cons_columns   ucc
               JOIN user_constraints    uc
                 ON ucc.constraint_name = uc.constraint_name
                AND uc.constraint_type  = UPPER('p')
          )                             d
       ON d.table_name  = b.table_name
      AND d.column_name = b.column_name
          --where a.TABLE_NAME = 'TAB_QUALITY_INFO'
 ORDER BY a.table_name 
        , a.column_id ASC ;

dba 用户导出

   SELECT a.table_name
        , c.comments
        , a.column_name
        , b.comments
        , a.data_type
        , a.data_length
        , a.data_precision
        , a.data_scale 
        , d.position
     FROM dba_tab_cols                  a
LEFT JOIN dba_col_comments              b
       ON b.table_name  = a.table_name
      AND b.column_name = a.column_name
      AND a.owner       = b.owner
LEFT JOIN dba_tab_comments              c
       ON c.table_name = b.table_name
      AND c.owner      = b.owner
LEFT JOIN
          ( SELECT dcc.table_name
                  , dcc.column_name
                  , dcc.position
                  , dcc.owner
               FROM dba_cons_columns    dcc
               JOIN dba_constraints     dc
                 ON dcc.constraint_name = dc.constraint_name
                AND dc.constraint_type  = UPPER('p')
                AND dcc.owner           = dc.owner
          )                             d
       ON d.table_name  = b.table_name
      AND d.column_name = b.column_name
      AND d.owner       = a.owner
    WHERE a.owner       = UPPER('test')
          --and a.TABLE_NAME = 'TAB_QUALITY_INFO'
      AND a.table_name IN ('B_RKJBXX_CUR', 'C_RKJBXX_CUR', 'DIM_ODS_RKJBXX_CUR_CODEMAP', 'DIM_RKJBXX_PRIORITY', 'JKCSB', 'ODS_RKJBXX_CUR', 'TAB_QUALITY_INFO', 'TEST', 'TEST1', 'THE_CONF', 'TMP1', 'TMP2')
 ORDER BY a.table_name 
        , a.column_id ASC;