[ oracle / 오라클 ] 테이블 정의서 생성 쿼리 / 컬럼 정보 조회 / pk정보 조회

 

테이블 정의서를 DB로 조회하여 생성하는 방법입니다. 

즉, 모든 테이블명과 칼럼 정보를 조회하는 쿼리입니다.

 

1. 테이블, 테이블코멘트, 컬럼명, 데이터 타입, 길이, null여부, 컬럼코멘트를 가져오는 쿼리

SELECT 
--	   C.TABLE_TYPE AS "테이블타입",
	   A.TABLE_NAME AS "테이블",
	   C.COMMENTS AS "테이블명",
       A.COLUMN_NAME AS "컬럼",
       B.COMMENTS AS "컬럼명",
       A.DATA_TYPE AS "데이터타입",
       A.DATA_LENGTH AS "길이",
       A.NULLABLE AS "Null 여부"
FROM   dba_tab_columns A
       LEFT JOIN all_col_comments B ON A.OWNER = B.OWNER 
       AND    A.TABLE_NAME = B.TABLE_NAME
       AND    A.COLUMN_NAME = B.COLUMN_NAME
	LEFT JOIN ALL_TAB_COMMENTS C ON A.OWNER = C.OWNER AND  A.TABLE_NAME = C.TABLE_NAME
       WHERE 1=1
       AND    A.OWNER = '스키마명입력'  -- DB명
--       AND  A.TABLE_NAME = '테이블명입력' --테이블
--AND C.TABLE_TYPE <> 'TABLE' --테이블타입선택 talbe/view
AND C.TABLE_TYPE = 'TABLE'
ORDER BY A.TABLE_NAME;

 

2. 1번 내용에 pk까지 추가한 쿼리

WITH LIST AS
(
  SELECT 
	   A.TABLE_NAME,
	   TB1.COMMENTS AS "TB_TABLE",
       A.COLUMN_NAME,
       B.COMMENTS,
       A.DATA_TYPE,
       A.DATA_LENGTH,
       A.NULLABLE
FROM   dba_tab_columns A
       LEFT JOIN all_col_comments B ON A.OWNER = B.OWNER 
       AND    A.TABLE_NAME = B.TABLE_NAME
       AND    A.COLUMN_NAME = B.COLUMN_NAME
	LEFT JOIN ALL_TAB_COMMENTS TB1 ON A.OWNER = TB1.OWNER AND  A.TABLE_NAME = TB1.TABLE_NAME
       WHERE 1=1
       AND    A.OWNER = '스키마명입력'  -- DB명
--       AND  A.TABLE_NAME = '테이블명입력' --테이블
--AND C.TABLE_TYPE <> 'TABLE'
AND TB1.TABLE_TYPE = 'TABLE' --테이블타입입력 table/view
),
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.TB_TABLE AS "테이블명",
       L.COLUMN_NAME AS "컬럼명",
       L.DATA_TYPE AS "데이터타입",
       L.DATA_LENGTH AS "길이",
       CASE WHEN P.POSITION < 99 THEN 'PK'
            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);