PK, Index 조회

출처 : http://blog.naver.com/alexzz/20174631971

 

/* PK조회 */

SELECT A.TABLE_NAME,

       B.COLUMN_NAME,

       B.DATA_TYPE,

       B.DATA_LENGTH,

       B.NULLABLE,

       B.DATA_DEFAULT,

       (SELECT A1.CONSTRAINT_TYPE

         FROM ALL_CONSTRAINTS A1, ALL_CONS_COLUMNS A2

        WHERE UPPER (A1.table_name) = A.TABLE_NAME

             AND UPPER (A1.owner) = A.OWNER

             AND A2.COLUMN_NAME = B.COLUMN_NAME

             AND A1.TABLE_NAME = A2.TABLE_NAME

             AND A1.OWNER = A2.OWNER

             AND A1.CONSTRAINT_NAME = A2.CONSTRAINT_NAME

             AND A1.CONSTRAINT_TYPE = 'P') as IS_PK

   FROM ALL_TABLES A, ALL_TAB_COLUMNS B

  WHERE A.TABLE_NAME = B.TABLE_NAME

    AND A.OWNER = B.OWNER

    AND UPPER (A.table_name) = '테이블명'

    AND UPPER (A.owner) = '오너'

 ORDER BY B.TABLE_NAME, B.COLUMN_ID;

/* Index 조회 */

SELECT index_name,un,

           REPLACE (SUBSTR (MAX (SYS_CONNECT_BY_PATH (column_name, '/')), 2),'/',',') idex_columns

FROM (SELECT c.index_name,

i.uniqueness un,

c.column_name,

c.column_position rn

FROM user_indexes i, user_ind_columns c

WHERE i.index_name = c.index_name

AND i.table_name = UPPER ('테이블명'))

START WITH rn = 1

CONNECT BY PRIOR index_name = index_name AND PRIOR rn + 1 = rn

GROUP BY index_name, un

ORDER BY un DESC, index_name;