Oracle获取表结构(包括主键)

WITH TMP_PK AS (--根据PK主键 把某用户名下的某些表导出
  SELECT A.OWNER
        ,A.TABLE_NAME
        ,A.COLUMN_NAME
        ,COUNT(1)   PK_NUM  --计算该表是否已有主键
    FROM ALL_CONS_COLUMNS A
    JOIN ALL_CONSTRAINTS B
      ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
     AND A.OWNER = B.OWNER
     AND B.OWNER = 'SCOTT'
   WHERE B.CONSTRAINT_TYPE = 'P' -- CONSTRAINT_TYPE = 'P' 选出约束类型是PRIMARY KEY
     AND A.OWNER = 'SCOTT'
   GROUP BY A.OWNER
           ,A.TABLE_NAME
           ,A.COLUMN_NAME
)
, TMP_UNI AS (
 SELECT A.OWNER --根据UNIQUE唯一索引，把某用户名下的某些表导出
        ,A.TABLE_NAME
        ,B.COLUMN_NAME
    FROM ALL_INDEXES A
    JOIN ALL_IND_COLUMNS B
      ON A.INDEX_NAME = B.INDEX_NAME
     AND B.TABLE_OWNER = 'SCOTT'
   WHERE A.OWNER = 'SCOTT'
     AND A.UNIQUENESS = 'UNIQUE' --把唯一索引选出来
     )
     
SELECT A.OWNER
        ,A.TABLE_NAME
        ,A.COLUMN_ID
        ,A.COLUMN_NAME
        ,(CASE
           WHEN A.DATA_TYPE = 'NUMBER' AND A.DATA_PRECISION IS NULL AND A.DATA_SCALE = 0 
                THEN 'INT'
           WHEN A.DATA_TYPE = 'NUMBER' AND A.DATA_PRECISION IS NULL AND A.DATA_SCALE IS NULL 
                THEN 'NUMBER'
           WHEN A.DATA_TYPE = 'NUMBER' AND A.DATA_PRECISION IS NOT NULL 
                THEN 'NUMBER(' || A.DATA_PRECISION || ',' || A.DATA_SCALE || ')'
           WHEN A.DATA_TYPE = 'TIMESTAMP(6)' 
                THEN 'TIMESTAMP'
           WHEN A.DATA_TYPE = 'VARCHAR2' 
                THEN 'VARCHAR2(' || DATA_LENGTH || ')'
           WHEN A.DATA_TYPE = 'CHAR' 
                THEN 'CHAR(' || DATA_LENGTH || ')'
           WHEN A.DATA_TYPE IN ('CLOB', 'BLOB', 'DATE') 
                THEN A.DATA_TYPE
           ELSE 'VARCHAR2(4000)'
         END )AS DATA_TYPE
        ,B.COMMENTS
        , CASE WHEN PK1.PK_NUM > 0   --若该表有主键，直接取
                    THEN (CASE WHEN PK.COLUMN_NAME IS NOT NULL THEN '是' ELSE '否' END)
               ELSE (CASE WHEN UNI.COLUMN_NAME IS NOT NULL THEN '是' ELSE '否' END) --无主键，有唯一索引则取唯一索引做主键  
               END AS V_PRIMARY1             
        ,DECODE(A.NULLABLE, 'Y', '否' , '是')AS NULLABLE     --是否可为空
    FROM ALL_TAB_COLS A
    JOIN ALL_COL_COMMENTS B
      ON A.TABLE_NAME = B.TABLE_NAME
     AND A.COLUMN_NAME = B.COLUMN_NAME
     AND A.OWNER = B.OWNER
    LEFT JOIN TMP_PK PK
      ON A.OWNER = PK.OWNER
     AND A.TABLE_NAME = PK.TABLE_NAME
     AND A.COLUMN_NAME = PK.COLUMN_NAME
    LEFT JOIN TMP_PK PK1
      ON A.OWNER = PK1.OWNER
     AND A.TABLE_NAME = PK1.TABLE_NAME
    LEFT JOIN TMP_UNI UNI
      ON A.OWNER = UNI.OWNER
     AND A.TABLE_NAME = UNI.TABLE_NAME
     AND A.COLUMN_NAME = UNI.COLUMN_NAME
   WHERE A.OWNER = 'SCOTT'
     AND A.TABLE_NAME IN ('表名')
   ORDER BY A.TABLE_NAME
           ,A.COLUMN_ID