오라클 인덱스(INDEX) 조회 쿼리

오라클에서 인덱스 조회 시

ALL_IND_COLUMNS 테이블에 TABLE_NAME에 테이블명을 넣고

조회를 하면 인덱스명을 구할 수 있기는 한데

 

실제 필요한 것은 인덱스명, 인덱스 컬럼 말고도

테이블, 컬럼 코멘트, 데이터 타입, PK, NULL, 등 많으므로

하나씩 JOIN해가며 작성하려면 손이 많이 가고 귀찮다

 

테이블 명만 바꾸면 바로 조회가 가능하게

쿼리를 작성해 놨는데

실 사용 시에는 하단 WHERE절의

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

 

  SELECT   A.TABLE_NAME -- 테이블명
         , (SELECT COMMENTS FROM ALL_TAB_COMMENTS X WHERE X.TABLE_NAME = A.TABLE_NAME AND X.OWNER = A.TABLE_OWNER) AS TABLE_COMMENT -- 테이블 COMMENT
         , A.INDEX_NAME -- 인덱스명
         , A.COLUMN_POSITION -- 인덱스 컬럼 번호
         , A.COLUMN_NAME -- 컬럼명
         , B.DATA_TYPE || -- 데이터타입
           CASE WHEN B.DATA_TYPE IN ('CHAR','NVARCHAR2','VARCHAR2') THEN '('||B.CHAR_LENGTH||' '||DECODE(B.CHAR_USED,'B','BYTE','CHAR')||')'
           WHEN B.DATA_TYPE IN ('NUMBER','NUMERIC') THEN '('||NVL2(B.DATA_PRECISION,B.DATA_PRECISION,B.DATA_LENGTH)||', '||NVL(B.DATA_SCALE,'0')||')' END AS DATA_TYPE         
         , (SELECT COMMENTS FROM ALL_COL_COMMENTS X WHERE X.TABLE_NAME = B.TABLE_NAME AND X.COLUMN_NAME = B.COLUMN_NAME AND X.OWNER = B.OWNER) AS COLUMN_COMMENT -- 테이블 컬럼 COMMENT
         , (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 = B.TABLE_NAME AND X.OWNER = B.OWNER AND Y.COLUMN_NAME = B.COLUMN_NAME) AS PRIMARY_KEY -- PK
         , DECODE(B.NULLABLE,'Y','NULL','NOT NULL') AS NULLABLE -- NULL 가능 여부
         , B.DATA_DEFAULT -- 기본값
    FROM ALL_IND_COLUMNS A, ALL_TAB_COLUMNS B
   WHERE A.INDEX_OWNER = B.OWNER
     AND A.TABLE_NAME = B.TABLE_NAME
     AND A.COLUMN_NAME = B.COLUMN_NAME
     AND A.TABLE_NAME = 'EMP' -- 검색하려는 테이블 입력
ORDER BY A.INDEX_NAME, A.COLUMN_POSITION