[Oracle] 테이블 정의서 추출 쿼리

--테이블 정의서 추출 쿼리
--우선 코맨트를 전부 넣은 상태에서 실행해야 합니다. 
COMMENT ON TABLE    스키마명.테이블명 IS '테이블설명';
COMMENT ON COLUMN 스키마명.테이블명.컬럼명 IS '컬럼설명';

-- 테이블정의서 만들기
SELECT /*+ ordered */
       A1.TABLE_COMMENTS
     , A1.TABLE_NAME
     , A1.COLUMN_ID
     , A1.COLUMN_NAME
     , A1.COLUMN_COMMENTS
     , (CASE WHEN B1.CONSTRAINT_TYPE = 'P'
             THEN 'Y'
        END) AS PK_FLAG
     , NVL(A1.NULL_FLAG, 'N') AS NULL_FLAG 
     , A1.DATA_TYPE||(CASE A1.DATA_TYPE WHEN 'NUMBER' 
                                        THEN CASE WHEN A1.DATA_SCALE IS NULL
                                                  THEN '('||TO_CHAR(A1.DATA_PRECISION)||')'
                                                  WHEN A1.DATA_SCALE = 0
                                                  THEN '('||TO_CHAR(A1.DATA_PRECISION)||')'
                                                  ELSE '('||TO_CHAR(A1.DATA_PRECISION)||','||TO_CHAR(A1.DATA_SCALE)||')'
                                                  END
                                        WHEN 'DATE'   
                                        THEN '' 
                                        ELSE '('||A1.DATA_LENGTH||')' 
                      END) AS DATA_TYPE 
  FROM (SELECT B.COMMENTS TABLE_COMMENTS
             , A.TABLE_NAME TABLE_NAME
             , C.COMMENTS COLUMN_COMMENTS
             , A.COLUMN_NAME COLUMN_NAME
             , (CASE A.NULLABLE
                WHEN 'Y'
                THEN 'Y'
                END) NULL_FLAG
             , A.DATA_TYPE DATA_TYPE
             , A.DATA_LENGTH 
             , A.COLUMN_ID AS COLUMN_ID
             , A.DATA_PRECISION
          FROM USER_TAB_COLUMNS A
             , USER_TAB_COMMENTS B
             , USER_COL_COMMENTS C
         WHERE (A.TABLE_NAME = B.TABLE_NAME)
           AND (A.TABLE_NAME = C.TABLE_NAME AND A.COLUMN_NAME = C.COLUMN_NAME)
           AND B.TABLE_TYPE = 'TABLE'
       ) A1
     , (SELECT A.TABLE_NAME
             , A.COLUMN_NAME
             , B.CONSTRAINT_TYPE
          FROM USER_CONS_COLUMNS A
             , USER_CONSTRAINTS B
         WHERE (A.CONSTRAINT_NAME = B.CONSTRAINT_NAME)
           AND B.CONSTRAINT_TYPE IN ('P', 'R')) B1
 WHERE (A1.TABLE_NAME = B1.TABLE_NAME(+) AND A1.COLUMN_NAME = B1.COLUMN_NAME(+))
  --AND A1.TABLE_NAME LIKE 'VMS%'
 ORDER BY A1.TABLE_NAME, A1.COLUMN_ID;
 
 
-- 테이블 목록 만들기(대략적인 크기로..)
SELECT TA1.TABLE_NAME AS TALBE_ID, 
       TA1.COMMENTS AS TABLE_NAME, 
       TA2.DATA_LENGTH AS DATA_LENGTH,
       TA3.MAX_NUM AS MAX_NUM,
       TA3.INITIAL_NUM AS INITIAL_NUM,
       (TA2.DATA_LENGTH*TA3.MAX_NUM) AS EXTEND_NUM
  FROM
       (SELECT  TABLE_NAME, COMMENTS FROM USER_TAB_COMMENTS WHERE TABLE_TYPE = 'TABLE') TA1,
       (SELECT  TABLE_NAME, SUM(DATA_LENGTH) AS DATA_LENGTH FROM USER_TAB_COLUMNS GROUP BY TABLE_NAME) TA2,
       (SELECT  TABLE_NAME
        ,(CASE 
              WHEN (NUM_ROWS < 1000) THEN 1000
              WHEN (10000   > NUM_ROWS) AND (NUM_ROWS > 1000)   THEN 10000
              WHEN (100000  > NUM_ROWS) AND (NUM_ROWS > 10000)  THEN 100000
              WHEN (1000000 > NUM_ROWS) AND (NUM_ROWS > 100000) THEN 1000000
              ELSE 10000000
          END    ) AS MAX_NUM
        , INITIAL_EXTENT AS INITIAL_NUM
        , GREATEST(4, CEIL(NUM_ROWS / 
                     DECODE(((ROUND(((1958 - (INI_TRANS * 23)) * ((100 - PCT_FREE) /100)) / 
                                    DECODE(AVG_ROW_LEN,0,1,AVG_ROW_LEN))))*2,0,1,
                        ((ROUND(((1958 - (INI_TRANS * 23)) * ((100 - PCT_FREE) /100)) / 
                                    DECODE(AVG_ROW_LEN,0,1,AVG_ROW_LEN))))*2)) * 2)
          AS TABLESIZE_KBYTES
           FROM  USER_TABLES) TA3
WHERE TA1.TABLE_NAME = TA2.TABLE_NAME
  AND TA1.TABLE_NAME = TA3.TABLE_NAME 
ORDER BY TA1.TABLE_NAME