오라클 컬럼 타입 조회 - olakeul keolleom taib johoe

1. 테이블 조회
  1) 테이블 목록 조회
     SELECT * FROM all_all_tables;

     SELECT * FROM dba_tables;

     SELECT * FROM ALL_OBJECTS 
     WHERE OBJECT_TYPE = 'TABLE';

  2) 접속한 계정의 테이블 목록 조회
     SELECT * FROM tabs;

       SELECT * FROM USER_OBJECTS 
     WHERE OBJECT_TYPE = 'TABLE';

       SELECT * FROM USER_TABLES;

  3) 테이블 코멘트 조회
     SELECT * FROM ALL_TAB_COMMENTS

     WHERE TABLE_NAME = '테이블명';

       SELECT * FROM USER_TAB_COMMENTS;

2. 컬럼 조회
  1) 컬럼 조회
     SELECT * FROM COLS 
     WHERE TABLE_NAME = '테이블명';

     SELECT * FROM ALL_TAB_COLUMNS 
     WHERE TABLE_NAME = '테이블명';

     SELECT * FROM USER_TAB_COLUMNS;

  2) 컬럼 코멘트 조회
     SELECT * FROM USER_COL_COMMENTS;

오라클에서 여러 테이블의

컬럼, 타입, PK, NULL 등을 확인 시에

테이블 하나씩 들어가서 확인해보려면

엄청 오랜 시간이 걸리는데

해당 쿼리를 사용하면

테이블의 컬럼, 데이터타입, PK, NULL, 기본값, 코멘트 등을

한번에 확인할 수 있다

쿼리는 아래 내용에서

테이블명만 바꿔 사용해주면 된다

   SELECT A.TABLE_NAME
         , (SELECT COMMENTS FROM ALL_TAB_COMMENTS X WHERE X.TABLE_NAME = A.TABLE_NAME AND X.OWNER = A.OWNER) AS TABLE_COMMENT
         , COLUMN_ID
         , A.COLUMN_NAME
         , A.DATA_TYPE ||
           CASE WHEN DATA_TYPE IN ('CHAR','NVARCHAR2','VARCHAR2') THEN '('||CHAR_LENGTH||' '||DECODE(CHAR_USED,'B','BYTE','CHAR')||')'
           WHEN DATA_TYPE IN ('NUMBER','NUMERIC') THEN '('||NVL2(DATA_PRECISION,DATA_PRECISION,DATA_LENGTH)||', '||NVL(DATA_SCALE,'0')||')' END AS DATA_TYPE
         , (SELECT POSITION FROM ALL_CONSTRAINTS X, ALL_CONS_COLUMNS Y WHERE X.CONSTRAINT_TYPE = 'P' AND X.CONSTRAINT_NAME = Y.CONSTRAINT_NAME AND X.TABLE_NAME = A.TABLE_NAME AND X.OWNER = A.OWNER AND Y.COLUMN_NAME = A.COLUMN_NAME) AS Primary_key
         , DECODE(A.NULLABLE,'Y','NULL','NOT NULL') AS NULLABLE
         , DATA_DEFAULT
         , (SELECT COMMENTS FROM ALL_COL_COMMENTS X WHERE X.TABLE_NAME = A.TABLE_NAME AND X.COLUMN_NAME = A.COLUMN_NAME AND X.OWNER = A.OWNER) AS COLUMN_COMMENT
    FROM ALL_TAB_COLUMNS A, ALL_COL_COMMENTS B
   WHERE A.TABLE_NAME = B.TABLE_NAME
     AND A.COLUMN_NAME = B.COLUMN_NAME
     AND A.TABLE_NAME = 'EMP' -- 테이블명
ORDER BY A.TABLE_NAME, A.COLUMN_ID

프로젝트를 하고나면 산출물로 나오는 문서중에 테이블 정의서를 자주 만들고는 합니다.

테이블 정의서를 만들기 위해서 테이블을 하나하나씩 찾아가서

COLUMN 과 COMMENT 를 복사해서 엑셀 문서에 붙여넣기를 하곤 하는데

앞으로는 그렇게 하지 말기 위해서 SQL 문을 작성해두었습니다.

아래 SQL 을 가공하여 사용하면 더 쉬운 문서 한벌이 만들어 질 수 있으니 고효율이 아닐까요

SELECT T1.TABLE_NAME
     , T3.COMMENTS AS TABLE_COMMENTS
     , T1.COLUMN_NAME
     , T2.COMMENTS AS COLUMN_COMMENTS
     , CASE WHEN T1.DATA_TYPE IN ('LONG', 'CLOB', 'DATE') THEN DATA_TYPE
            WHEN T1.DATA_TYPE = 'VARCHAR2' THEN DATA_TYPE || '(' || DATA_LENGTH || ' BYTE)'
            WHEN T1.DATA_TYPE = 'NUMBER'   THEN DATA_TYPE || '(' || DATA_LENGTH || ' BYTE)'
            ELSE T1.DATA_TYPE
             END AS DATA_TYPE
  FROM ALL_TAB_COLUMNS T1
 INNER JOIN USER_COL_COMMENTS T2
    ON T1.TABLE_NAME = T2.TABLE_NAME
   AND T1.COLUMN_NAME = T2.COLUMN_NAME   
 INNER JOIN USER_TAB_COMMENTS T3
    ON T1.TABLE_NAME = T3.TABLE_NAME
 WHERE OWNER = 'SCOTT'
   AND T1.TABLE_NAME = 'EMP'
 ORDER BY T1.TABLE_NAME, T1.COLUMN_ID

위의 SQL 만 놓고 보면 특정 한 개의 테이블을 조회를 하는것으로 보이지만

조회조건에서 TABLE_NAME 을 제거한다면

특정 스키마에 존재하는 테이블의 COLUMN 과 COMMENT, 그리고 DATA_TYPE 까지 한번에 조회할 수 있습니다.

테이블 정의서에 맞게 해당 SQL 을 가공하여 사용하면 시간을 조금이나마 아낄 수 있게 됩니다.