[Oracle] pk, index 리스트 조회 쿼리

 

/* Formatted on 2013/06/19 11:08:02 (QP5 v5.227.12220.39724) */

/* 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')

            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),

                    '/',

                    ',')

              index_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;

 

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