Oracle查询数据库中的所有表

 SELECT A.TABLE_NAME 表英文名,
       A.TAB_COMMENTS 表中文名,
       A.COLUMN_ID 序号,
       A.COLUMN_NAME 英文名,
       A.COMMENTS 中文名,
       B.PRIMARY_KEY 主键,
       substr(A.DATA_TYPE,1,instr(A.DATA_TYPE,'(')-1) 数据类型,
       case when substr(A.DATA_TYPE,1,instr(A.DATA_TYPE,'(')-1) = 'TIMESTAMP' then '' when substr(A.DATA_TYPE,1,instr(A.DATA_TYPE,'(')-1) = 'DATATIME' then '' else  substr(A.DATA_TYPE,instr(A.DATA_TYPE,'(')+1,length(A.DATA_TYPE)-instr(A.DATA_TYPE,'(')-1) end 长度,
       '' 单位,
       A.NULLABLE 是否可空,
       '' 缺省,
       '' 上限,
       '' 下限
      
  FROM (SELECT X.TABLE_NAME,
               Z.COMMENTS TAB_COMMENTS,
               X.COLUMN_ID,
               X.COLUMN_NAME,
               X.DATA_TYPE ||
               DECODE(X.DATA_TYPE,
                      'NUMBER',
                      DECODE(SIGN(X.DATA_SCALE),
                             0,
                             '(' ||
                             DECODE(LENGTH(TO_CHAR(X.DATA_PRECISION)),
                                    1,
                                    TO_CHAR(X.DATA_PRECISION),
                                    TO_CHAR(X.DATA_PRECISION, 'FM00')) || ')',
                             1,
                             '(' ||
                             DECODE(LENGTH(TO_CHAR(X.DATA_PRECISION)),
                                    1,
                                    TO_CHAR(X.DATA_PRECISION),
                                    TO_CHAR(X.DATA_PRECISION, 'FM00')) || ',' ||
                             TO_CHAR(X.DATA_SCALE, 'FM0') || ')',
                             ''),
                      '(' || DECODE(LENGTH(TO_CHAR(X.DATA_LENGTH)),
                                    1,
                                    TO_CHAR(X.DATA_LENGTH),
                                    TO_CHAR(X.DATA_LENGTH, 'FM9900')) || ')') AS DATA_TYPE,
               X.NULLABLE,
               Y.COMMENTS
          FROM USER_TAB_COLUMNS X, USER_COL_COMMENTS Y, USER_TAB_COMMENTS Z
         WHERE X.TABLE_NAME = Y.TABLE_NAME
           AND X.COLUMN_NAME = Y.COLUMN_NAME
           AND X.TABLE_NAME = Z.TABLE_NAME) A,
       (SELECT X.TABLE_NAME TABLE_NAME,
               X.COLUMN_NAME COLUMN_NAME,
               MAX(DECODE(Y.CONSTRAINT_TYPE,
                          'P',
                          'PK' || TO_CHAR(X.POSITION, 'FM9'),
                          '')) PRIMARY_KEY,
               MAX(DECODE(Y.CONSTRAINT_TYPE,
                          'R',
                          'FK' || TO_CHAR(X.POSITION, 'FM9'),
                          '')) FOREIGN_KEY
          FROM USER_CONS_COLUMNS X, USER_CONSTRAINTS Y
         WHERE X.TABLE_NAME = Y.TABLE_NAME
           AND X.CONSTRAINT_NAME = Y.CONSTRAINT_NAME
           AND Y.CONSTRAINT_TYPE <> 'C'
         GROUP BY X.TABLE_NAME, X.COLUMN_NAME) B
 WHERE A.TABLE_NAME = B.TABLE_NAME(+)
   AND A.COLUMN_NAME = B.COLUMN_NAME(+)
   /*AND A.table_name = upper('tp_planmonth')*/
 ORDER BY A.TABLE_NAME, A.COLUMN_ID

转载于:https://www.cnblogs.com/pipi-aspdotnet/p/3713620.html