오라클 테이블 정의서, 정보조회 쿼리!

회사에서 일을 하다보면 산출물이나 해당 테이블에 대한 정보를 다른 회사와 협업하기위해 제출해야할 경우가 있다.

db툴로 들어가서 해당하는 DB를 엑셀파일로 하나하나 만들어 줄 수도있지만...그건너무 비효율적이였다

아래와 같이 쿼리들을 찾아서 떼와서 가공하는게 훨씬 쉬웠다!

 

테이블정의서 자동으로 만들어주는 오라클 쿼리입니다.

 

SELECT A.TABLE_NAME AS "테이블명",

       A.COLUMN_NAME AS "컬럼명",

       A.DATA_TYPE AS "데이터타입",

       A.DATA_LENGTH AS "길이",

       A.NULLABLE AS "Null 여부",

       B.COMMENTS AS "Comments"

FROM   dba_tab_columns A,

       all_col_comments B

WHERE  A.OWNER = B.OWNER

AND    A.TABLE_NAME = B.TABLE_NAME

AND    A.COLUMN_NAME = B.COLUMN_NAME

AND    A.OWNER = 'LOTOSDEV'  -- DB명

ORDER BY A.TABLE_NAME

 

 

https://zzznara2.tistory.com/720

 

 

PK가 추가로 표시된 값을 표출해주는 쿼리

 

WITH LIST AS

(

    SELECT A.TABLE_NAME,

           A.COLUMN_NAME,

           A.DATA_TYPE,

           A.DATA_LENGTH,

           A.NULLABLE,

           B.COMMENTS

    FROM   dba_tab_columns A,

           all_col_comments B

    WHERE  A.OWNER = B.OWNER

    AND    A.TABLE_NAME = B.TABLE_NAME

    AND    A.COLUMN_NAME = B.COLUMN_NAME

    AND    A.OWNER = 'LOTOSDEV' -- DB명

),

PKLIST AS

(

    SELECT C.TABLE_NAME,

           C.COLUMN_NAME,

           C.POSITION

    FROM USER_CONS_COLUMNS C,

         USER_CONSTRAINTS S

    WHERE C.CONSTRAINT_NAME = S.CONSTRAINT_NAME

    AND S.CONSTRAINT_TYPE = 'P'

)

SELECT L.TABLE_NAME AS "테이블명",

       L.COLUMN_NAME AS "컬럼명",

       L.DATA_TYPE AS "데이터타입",

       L.DATA_LENGTH AS "길이",

       CASE WHEN P.POSITION < 99 THEN 'Y'

            ELSE ' '

       END AS "PK",

       L.NULLABLE AS "Null 여부",

       L.COMMENTS AS "Comments"

FROM LIST L,

     PKLIST P

WHERE L.TABLE_NAME = P.TABLE_NAME(+)

  AND L.COLUMN_NAME = P.COLUMN_NAME(+)

 ORDER BY L.TABLE_NAME,

          NVL(P.POSITION, 99)

;

 

출처: <https://zzznara2.tistory.com/720>

 

 

테이블 정보 조회쿼리!

 

TABLE_COMMENTS   (테이블 코멘트)

TABLE_NAME       (테이블명)

COLUMN_COMMENTS  (컬럼 코멘트)

COLUMN_NAME      (컬럼명)

PK_FLAG          (pk 여부)

FK_FLAG          (fk 여부)

NULL_FLAG        (null 여부)

DATA_TYPE        (테이터타입)

DATA_LENGTH      (데이터 길이)

 

SELECT A1.TABLE_COMMENTS TABLE_COMMENTS

     , A1.TABLE_NAME TABLE_NAME

     , A1.COLUMN_COMMENTS COLUMN_COMMENTS

     , A1.COLUMN_NAME COLUMN_NAME

     , (CASE

           WHEN B1.CONSTRAINT_TYPE = 'P'

              THEN 'Y'

        END) PK_FLAG

     , (CASE

           WHEN B1.CONSTRAINT_TYPE = 'R'

              THEN 'Y'

        END) FK_FLAG

     , A1.NULL_FLAG

     , A1.DATA_TYPE

     , A1.DATA_LENGTH

  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

             , (CASE

                   WHEN A.DATA_TYPE IN ('CHAR', 'VARCHAR2')

                      THEN '(' || A.DATA_LENGTH || ')'

                   WHEN A.DATA_TYPE = 'NUMBER'

                   AND A.DATA_SCALE = 0

                   AND A.DATA_PRECISION IS NOT NULL

                      THEN '(' || A.DATA_PRECISION || ')'

                   WHEN A.DATA_TYPE = 'NUMBER'

                   AND A.DATA_SCALE <> 0

                      THEN '(' || A.DATA_PRECISION || ',' || A.DATA_SCALE

                           || ')'

                END

               ) DATA_LENGTH

 

             , A.COLUMN_ID

          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

               )) 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(+))

ORDER BY A1.TABLE_NAME, A1.COLUMN_ID

 

출처: https://cofs.tistory.com/68 [CofS]