[Toad] 테이블 정의서 Excel 로 뽑기

테이블 정의서 만들때 쓰는 쿼리 --우선 코맨트를 전부 넣은 상태에서 실행해야 합니다. COMMENT ON TABLE    스키마명.테이블명 IS '테이블설명'; COMMENT ON COLUMN 스키마명.테이블명.컬럼명 IS '컬럼설명'; -- 테이블 정의서 1번째 방법 SELECT A1.TABLE_COMMENTS            , A1.TABLE_NAME --        , A1.COLUMN_ID            , A1.COLUMN_NAME AS COLUMN_ID            , A1.COLUMN_COMMENTS AS COLUMN_NAME            , A1.DATA_TYPE AS DATA_TYPE                 , (CASE A1.DATA_TYPE WHEN 'NUMBER' THEN TO_CHAR(A1.DATA_LENGTH)                                               WHEN 'DATE'   THEN ' '                                               ELSE TO_CHAR(A1.DATA_LENGTH) END) AS DATA_LENGTH            , NVL(A1.NULL_FLAG, 'N') AS NULL_FLAG                                          , (CASE WHEN B1.CONSTRAINT_TYPE = 'P' THEN 'PK' END) PK_FLAG   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     -- 테이블정의서 만들기 2번째 방법 SELECT     X.TABLE_NAME TABLE_ID,     (SELECT COMMENTS FROM USER_TAB_COMMENTS                   WHERE TABLE_NAME = X.TABLE_NAME) TABLE_NM,     X.COLUMN_NAME FIELD_ID,     (SELECT COMMENTS FROM USER_COL_COMMENTS                   WHERE TABLE_NAME = X.TABLE_NAME AND COLUMN_NAME = X.COLUMN_NAME) FIELD_NAME,     X.DATA_TYPE AS "TYPE",     DECODE(X.DATA_TYPE,                  'DATE', NULL,                  'BLOB', NULL,                   X.DATA_LENGTH || CASE WHEN X.DATA_PRECISION IS NOT NULL THEN ',' || X.DATA_SCALE END )                   DATATYPES,     CASE WHEN SUBSTR(Y.INDEX_NAME, 1, 2) = 'PK' THEN 'PK'              ELSE DECODE(X.NULLABLE, 'N', 'NOT NULL', '') END "NOT NULL",     X.DATA_DEFAULT FROM COLS X, USER_IND_COLUMNS Y WHERE X.TABLE_NAME = Y.TABLE_NAME(+) AND X.COLUMN_NAME = Y.COLUMN_NAME(+) ORDER BY X.TABLE_NAME, X.COLUMN_ID   -- 테이블정의서 만들기 3번째 방법(주로 사용) SELECT A1.TABLE_COMMENTS            , A1.TABLE_NAME            , A1.COLUMN_ID            , A1.COLUMN_NAME            , A1.COLUMN_COMMENTS            , (CASE               WHEN B1.CONSTRAINT_TYPE = 'P'               THEN 'Y'               END) PK_FLAG            , NVL(A1.NULL_FLAG, 'N') AS NULL_FLAG             , A1.DATA_TYPE||              (CASE A1.DATA_TYPE               WHEN 'NUMBER'               THEN '('||TO_CHAR(A1.DATA_LENGTH)||','||TO_CHAR(A1.DATA_PRECISION)||')'               WHEN 'DATE'   THEN ' '                ELSE '('||A1.DATA_LENGTH||')'               END) 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