오라클 테이블정의서 생성쿼리

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;
출처 : http://egloos.zum.com/lune23/v/437445

You may also like...

답글 남기기

이메일 주소는 공개되지 않습니다. 필수 항목은 *(으)로 표시합니다