[Oracle]오라클에서 쿼리로 테이블정의서 추출하기

오늘은 오라클 테이블정의서를

쿼리로 추출하는 방법에 대해서 정리를 해보고자 합니다.

물론, DB툴등에서 테이블정의서를 확인할 수 있어서

이게 왜 필요할까라는 생각이 들 순 있을 것 같습니다.

 

 

그렇지만 DB툴을 사용하지 못한다던가

뭔가 상황이 있을 때를 대비해서

알아두면 좋을 것 같습니다.

 

제 개인적인 생각으로는 필요없는 지식은 없다라고 생각하기 때문에

알아두어서 나쁘지는 않을 것 같은 생각으로

정리를 해보고자 합니다.

 

반응형

 

1. 함수 생성

우선 Oracle에서 테이블정의서를 추출하기 위해서는

함수를 먼저 만들어야 합니다.

해당 함수를 가지고 테이블 정의서를 추출하는데

사용을 하기 때문입니다.

 

CREATE OR REPLACE FUNCTION FC_DATA_DEFAULT_GET (
   ORI_TABLENAME   VARCHAR2,
   ORI_COLUMN_NAME VARCHAR2
)
  RETURN VARCHAR
AS
   LONG_CONTENT   VARCHAR2 (32767);
   V_QUERY        VARCHAR2 (2000);

BEGIN
   V_QUERY :=
         'SELECT DATA_DEFAULT FROM COLS '||
         ' WHERE TABLE_NAME  = '||CHR(39)|| ORI_TABLENAME  ||CHR (39)||
         '   AND COLUMN_NAME = '||CHR(39)|| ORI_COLUMN_NAME||CHR(39);
 
   EXECUTE IMMEDIATE V_QUERY
                INTO LONG_CONTENT;
     
   LONG_CONTENT := SUBSTR (LONG_CONTENT, 1, 4000);
   RETURN LONG_CONTENT;
END;

 

위 쿼리를 이용하여 함수를 생성합니다.

그냥 함수를 생성하는 쿼리입니다.

위 쿼리를 그대로 복사해서 돌려주시면 되십니다.

 

 

 

위처럼 쿼리를 작성하였습니다.

그리고 실행을 하면 결과는 아래처럼 나오게 됩니다.

 

 

그냥 함수가 만들어졌다는 것입니다.

함수가 정상적으로 생성이 되었다면 이제 준비는 끝난것입니다.

 

 

 

728x90

 

2. 테이블정의서 확인

이제 Table 정의서를 확인하는 방법을 알아보도록 하겠습니다.

위 1번에서 만든 함수를 이용하여 쿼리를 돌리는 것입니다.

1번처럼 제가 올린 쿼리를 그대로 실행해주시면 되십니다.

 

쿼리는 다음과 같습니다.

SELECT A.TABLE_NAME AS "테이블명" 
     , D.COMMENTS AS "테이블설명" 
     , B.COLUMN_NAME AS "컬럼명" 
     , B.COMMENTS AS "컬럼설명" 
     , A.DATA_TYPE AS "타입" 
     , A.DATA_LENGTH AS "길이" 
     , A.NULLABLE AS "NULL?" 
     , DECODE(C.COLUMN_NAME, NULL, '', 'PK') AS "제약조건" 
     , DECODE(E.COLUMN_NAME, NULL, '', 'UK') AS UK 
     , TRIM(REPLACE (REPLACE (B.DATA_DEFAULT, CHR (10), '' ), CHR (13), '' )) AS "초기값" 
  FROM COLS A 
     , ( 
       SELECT COLUMN_NAME 
            , COMMENTS 
            , FC_DATA_DEFAULT_GET(TABLE_NAME, COLUMN_NAME) AS DATA_DEFAULT

            , TABLE_NAME 
         FROM USER_COL_COMMENTS 
     ) B, ( 
       SELECT A.TABLE_NAME 
            , A.COLUMN_NAME 
         FROM USER_IND_COLUMNS A 
            , USER_CONSTRAINTS B 
        WHERE A.INDEX_NAME = B.CONSTRAINT_NAME 
          AND B.CONSTRAINT_TYPE = 'P' 
     ) C, USER_TAB_COMMENTS D, ( 
       SELECT A.TABLE_NAME 
            , B.COLUMN_NAME 
         FROM USER_INDEXES A 
            , USER_IND_COLUMNS B 
        WHERE A.INDEX_NAME = B.INDEX_NAME 
          AND A.UNIQUENESS = 'UNIQUE' 
          AND A.INDEX_NAME NOT IN ( 
              SELECT A.INDEX_NAME 
                FROM USER_IND_COLUMNS A 
                   , USER_CONSTRAINTS B 
               WHERE A.INDEX_NAME = B.CONSTRAINT_NAME 
                 AND B.CONSTRAINT_TYPE = 'P' 
            ) 
     ) E 
 WHERE A.TABLE_NAME = B.TABLE_NAME 
   AND A.COLUMN_NAME = B.COLUMN_NAME 
   AND A.TABLE_NAME = C.TABLE_NAME (+) 
   AND A.COLUMN_NAME = C.COLUMN_NAME (+) 
   AND A.TABLE_NAME = D.TABLE_NAME 
   AND A.TABLE_NAME = E.TABLE_NAME (+) 
   AND A.COLUMN_NAME = E.COLUMN_NAME (+) 
   AND A.TABLE_NAME LIKE '테이블명' 
 ORDER BY A.TABLE_NAME, A.COLUMN_ID

 

위처럼 해주시면 되십니다.

위 쿼리에서 마지막에서 두번째줄에 보면

한글로 테이블명이라고 적어놓았습니다.

이 곳만 원하시는 테이블명으로 이름을 변경하고 돌려주시면 되십니다.

그러면 해당 테이블의 테이블정의서가 추출이 됩니다.

 

 

위처럼 쿼리를 작성하고 실행을 해주시면 되십니다.

테이블명은 제가 운영하는 db이기 때문에 숨겨놓았습니다.

위 쿼리를 돌리면 결과는 아래처럼 나오게 됩니다.

 

 

 

위처럼 테이블명, 설명, 컬럼명, 컬럼설명, 타입, 길이 등

여러가지 정보들이 나오게 됩니다.

 

이렇게 해서 테이블정의서를 확인할 수가 있습니다.

 

 

SMALL

 

 

오늘은 오라클에서 쿼리를 이용해서

Table정의서를 추추하는 방법에 대해서 정리를 해보았습니다.

 

위에서 얘기한 것처럼 별다른 쓸모가 없을 수도 있지만,

그래도 알고 있어서 나쁠 건 없는 것이지요.

 

혹시 나중에 필요한 상황이 있으시면 

사용을 해주시면 되실 것 같습니다.

 

 

 

 

이상으로 Oracle에서 쿼리를 이용하여

테이블정의서를 추출하는 방법이였습니다~

 

 

 

 

 

 

이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다.