[오라클] 테이블정의서 자동으로 만들어주는 Oracle 쿼리

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

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

이거 실행하고 엑셀로 저장하면 됩니다.

아래는 PK(Primary Key)까지 추가된 쿼리입니다.

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)
;

이 쿼리를 실행하면 아래처럼 PK 여부도 추가됩니다.