[ORACLE/오라클] 오라클 테이블 자동 DDL 생성 (테이블, PK, 인덱스)

데이터 흐름상 뒤에 위치하는

정보계나 테스트계을 맡아 프로젝트를 수행할 때, 

개발 중인 운영계의 오브젝트 변경사항이 발생할 수 있다.

담당자가 있음에도 불구하고 여러가지 사정으로

변경분이 적용된 오브젝트가 생성되지 않는 경우가 생기거나,

권한 문제로 패키지를 사용 못할 경우를 대비해 아래와 같이

간단하게 쿼리로 DDL문을 구현할 수 있다.

 

테이블 스페이스명이나

제약조건 중  PK외에 조건이 있을 경우

인덱스 종류를 추가해야 할 경우는 아래 쿼리를 상황에 맞게 적용하면 될거 같다.

/************************************************/
/* NAME : DDL CREATE
/* DATE : 2022.06.13
/* PURPOSE : 오라클 DDL문 생성
/************************************************/

WITH C1 AS (
SELECT T1.OWNER           AS OWNER
     , T1.TABLE_NAME      AS TABLE_NAME
     , T2.COMMENTS        AS TABLE_COMMENTS
     , T1.COLUMN_NAME     AS COLUMN_NAME
     , T3.COMMENTS        AS COLUMN_COMMENTS
     , T1.COLUMN_ID       AS COLUMN_ID
     , ROW_NUMBER()OVER(PARTITION BY T1.OWNER, T1.TABLE_NAME ORDER BY T1.COLUMN_ID DESC) AS COLUMN_REV_ID
     , CASE WHEN T1.DATA_TYPE LIKE '%CHAR%' THEN T1.DATA_TYPE||'('||T1.DATA_LENGTH||')'
            WHEN T1.DATA_TYPE    = 'NUMBER'  AND T1.DATA_PRECISION IS NOT NULL THEN T1.DATA_TYPE
            WHEN T1.DATA_TYPE    = 'NUMBER' THEN T1.DATA_TYPE||'('||T1.DATA_PRECISION||','||T1.DATA_SCALE||')'
            ELSE T1.DATA_TYPE
        END               AS DATA_TYPE
     , DECODE(T1.NULLABLE,'N','NOT NULL','NULL')      AS NULLABLE
     , T4.CONSTRAINT_NAME AS CONSTRAINT_NAME
     , T4.CONSTRAINT_TYPE AS CONSTRAINT_TYPE
     , T4."POSITION"      AS CONSTRAINT_POSITION
     , T5.INDEX_NAME      AS INDEX_NAME
     , T5.COLUMN_NAME     AS INDEX_COLUMN_NAME
     , T5.COLUMN_POSITION AS INDEX_COLUMN_POSITION
     , T5.DESCEND         AS INDEX_DESCEND
     , T5.INDEX_SEQ       AS INDEX_SEQ
  FROM ALL_TAB_COLS T1
       LEFT OUTER JOIN ALL_TAB_COMMENTS T2
               ON T1.OWNER      = T2.OWNER
              AND T1.TABLE_NAME = T2.TABLE_NAME
       LEFT OUTER JOIN ALL_COL_COMMENTS T3
               ON T1.OWNER       = T3.OWNER
              AND T1.TABLE_NAME  = T3.TABLE_NAME
              AND T1.COLUMN_NAME = T3.COLUMN_NAME
       LEFT OUTER JOIN (
                       SELECT T01.OWNER           AS OWNER
                            , T01.TABLE_NAME      AS TABLE_NAME
                            , T01.CONSTRAINT_TYPE AS CONSTRAINT_TYPE
                            , T01.CONSTRAINT_NAME AS CONSTRAINT_NAME
                            , T02.COLUMN_NAME     AS COLUMN_NAME
                            , T02."POSITION"      AS "POSITION"
                         FROM ALL_CONSTRAINTS T01
                              LEFT OUTER JOIN ALL_CONS_COLUMNS T02
                                      ON T01.OWNER           = T02.OWNER
                                     AND T01.TABLE_NAME      = T02.TABLE_NAME
                                     AND T01.CONSTRAINT_NAME = T02.CONSTRAINT_NAME
                        WHERE 1=1
                          AND T01.CONSTRAINT_TYPE = 'P' --제약조건 PRIMARY KEY
                       ) T4
                    ON T1.OWNER       = T4.OWNER
                   AND T1.TABLE_NAME  = T4.TABLE_NAME
                   AND T1.COLUMN_NAME = T4.COLUMN_NAME
       LEFT OUTER JOIN (
                       SELECT T01.OWNER           AS OWNER
                            , T01.INDEX_NAME      AS INDEX_NAME
                            , T01.TABLE_OWNER     AS TABLE_OWNER
                            , T01.TABLE_NAME      AS TABLE_NAME
                            , T02.COLUMN_NAME     AS COLUMN_NAME
                            , T02.COLUMN_POSITION AS COLUMN_POSITION
                            , T02.DESCEND         AS DESCEND
                            , COUNT(DISTINCT T01.INDEX_NAME)OVER() AS INDEX_SEQ
                         FROM ALL_INDEXES T01
                              LEFT OUTER JOIN ALL_IND_COLUMNS T02
                                      ON T01.OWNER       = T02.INDEX_OWNER
                                     AND T01.INDEX_NAME  = T02.INDEX_NAME
                                     AND T01.TABLE_OWNER = T02.TABLE_OWNER
                                     AND T01.TABLE_NAME  = T02.TABLE_NAME
                        WHERE 1=1
                          AND T01.INDEX_TYPE = 'NORMAL'    --비트맵 인덱스
                          AND T01.UNIQUENESS = 'NONUNIQUE' --유니크인덱스 제외
                       ) T5
                    ON T1.OWNER       = T5.TABLE_OWNER
                   AND T1.TABLE_NAME  = T5.TABLE_NAME
                   AND T1.COLUMN_NAME = T5.COLUMN_NAME
 WHERE 1=1
   AND T1.OWNER      = 'SCOTT'
   AND T1.TABLE_NAME IN ('BONUS','DEPT')
)

SELECT P1.TEXT AS TEXT
  FROM (
       --CREATE TABLE
       SELECT '1'            AS SEQ
            , T1.OWNER       AS OWNER
            , T1.TABLE_NAME  AS TABLE_NAME
            , T1.COLUMN_ID   AS COLUMN_ID
            , CASE WHEN T1.COLUMN_ID = 1    THEN            'CREATE TABLE '||OWNER||'.'||TABLE_NAME||' ('
                                                 ||CHR(10)||'  '||T1.COLUMN_NAME||' '||T1.DATA_TYPE||' '||T1.NULLABLE
                   WHEN T1.COLUMN_REV_ID = 1 THEN ', '||T1.COLUMN_NAME||' '||T1.DATA_TYPE||' '||T1.NULLABLE
                                                 ||CHR(10)||');'
                   ELSE ', '||T1.COLUMN_NAME||' '||T1.DATA_TYPE||' '||T1.NULLABLE
               END           AS TEXT
         FROM C1 T1
       
              UNION ALL
       --PK 생성       
       SELECT '2'
            , T1.OWNER
            , T1.TABLE_NAME
            , 0
            , 'ALTER TABLE '||T1.OWNER||'.'||T1.TABLE_NAME||' ADD CONSTRAINT '||T1.CONSTRAINT_NAME||' PRIMARY KEY ('||LISTAGG(T1.COLUMN_NAME, ',') WITHIN GROUP(ORDER BY T1.CONSTRAINT_POSITION        ASC)||');' AS COLUMN_NAME
         FROM C1 T1
        WHERE 1=1
          AND T1.CONSTRAINT_TYPE = 'P'
        GROUP BY T1.OWNER
            , T1.TABLE_NAME
            , T1.CONSTRAINT_NAME
            
              UNION ALL
       --인덱스 생성       
       SELECT '3'
            , T1.OWNER
            , T1.TABLE_NAME
            , 0
            , 'CREATE INDEX '||T1.INDEX_NAME||' ON '||T1.OWNER||'.'||T1.TABLE_NAME||'('||LISTAGG(T1.INDEX_COLUMN_NAME, ',') WITHIN GROUP(ORDER BY T1.INDEX_COLUMN_POSITION)||');'
         FROM C1 T1
        WHERE 1=1
          AND T1.INDEX_COLUMN_NAME IS NOT NULL
        GROUP BY T1.OWNER
            , T1.TABLE_NAME
            , T1.INDEX_COLUMN_NAME
            , T1.INDEX_NAME
       ) P1
 ORDER BY P1.OWNER ASC, P1.TABLE_NAME ASC, P1.SEQ ASC, P1.COLUMN_ID ASC