오라클 FK, PK 포함 스키마 조회하기

귀찮아서 저장중

20230328 number 타입 길이 관련 추가

SELECT A.OWNER
     , A.TABLE_NAME
     , A1.COMMENTS
     , A.COLUMN_NAME
     , B.COMMENTS
     , A.COLUMN_ID
     , A.DATA_TYPE
     , CASE WHEN A.DATA_TYPE = 'NUMBER' THEN '('||A.DATA_PRECISION||','||DATA_SCALE||')'
            WHEN A.DATA_TYPE = 'CLOB' THEN ''
            ELSE TO_CHAR(A.DATA_LENGTH)
        END DATA_LENGTH
     , A.NULLABLE
     , A.DATA_DEFAULT
     , C.CONSTRAINT_TYPE_PK
     , C.CONSTRAINT_TYPE_FK
  FROM ALL_TAB_COLUMNS A
  LEFT JOIN ALL_TAB_COMMENTS A1
    ON A.OWNER = A1.OWNER
   AND A.TABLE_NAME = A1.TABLE_NAME
  LEFT JOIN ALL_COL_COMMENTS B
    ON A.TABLE_NAME = B.TABLE_NAME 
   AND A.OWNER = B.OWNER
   AND A.COLUMN_NAME = B.COLUMN_NAME 
  LEFT JOIN (SELECT A.TABLE_NAME
                  , B.COLUMN_NAME     
                  , MAX(CASE WHEN A.CONSTRAINT_TYPE = 'P' THEN 'Y' END) AS CONSTRAINT_TYPE_PK
                  , MAX(CASE WHEN A.CONSTRAINT_TYPE = 'R' THEN 'Y' END) AS CONSTRAINT_TYPE_FK
               FROM ALL_CONSTRAINTS  A
                  , ALL_CONS_COLUMNS B
              WHERE A.TABLE_NAME      = B.TABLE_NAME
                AND (A.CONSTRAINT_TYPE = 'P' 
                 OR A.CONSTRAINT_TYPE = 'R')
                AND A.OWNER           = B.OWNER
                AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
              GROUP BY A.TABLE_NAME, B.COLUMN_NAME) C 
    ON A.TABLE_NAME = C.TABLE_NAME 
   AND A.COLUMN_NAME = C.COLUMN_NAME
 WHERE 1=1
   AND A.TABLE_NAME LIKE 'T_%'
   AND A.OWNER = 'OWNER'
 ORDER BY A.OWNER
     , A.TABLE_NAME
     , A.COLUMN_ID;