[MSSQL] 테이블의 인덱스 얻기

DECLARE @P_TABLE_NAME NVARCHAR(200);

SET @P_TABLE_NAME = 'MyTable';

SELECT CAST(CONVERT(SYSNAME, SI.NAME) AS VARCHAR(776)) AS INDEX_NM,
       CAST(CONVERT(SYSNAME, SC.NAME) AS VARCHAR(776)) AS COL_NM,
       CASE WHEN SC.NAME = INDEX_COL(@P_TABLE_NAME, SI.INDID,  1) THEN CONVERT(SMALLINT,1)
            WHEN SC.NAME = INDEX_COL(@P_TABLE_NAME, SI.INDID,  2) THEN CONVERT(SMALLINT,2)
            WHEN SC.NAME = INDEX_COL(@P_TABLE_NAME, SI.INDID,  3) THEN CONVERT(SMALLINT,3)
            WHEN SC.NAME = INDEX_COL(@P_TABLE_NAME, SI.INDID,  4) THEN CONVERT(SMALLINT,4)
            WHEN SC.NAME = INDEX_COL(@P_TABLE_NAME, SI.INDID,  5) THEN CONVERT(SMALLINT,5)
            WHEN SC.NAME = INDEX_COL(@P_TABLE_NAME, SI.INDID,  6) THEN CONVERT(SMALLINT,6)
            WHEN SC.NAME = INDEX_COL(@P_TABLE_NAME, SI.INDID,  7) THEN CONVERT(SMALLINT,7)
            WHEN SC.NAME = INDEX_COL(@P_TABLE_NAME, SI.INDID,  8) THEN CONVERT(SMALLINT,8)
            WHEN SC.NAME = INDEX_COL(@P_TABLE_NAME, SI.INDID,  9) THEN CONVERT(SMALLINT,9)
            WHEN SC.NAME = INDEX_COL(@P_TABLE_NAME, SI.INDID, 10) THEN CONVERT(SMALLINT,10)
            WHEN SC.NAME = INDEX_COL(@P_TABLE_NAME, SI.INDID, 11) THEN CONVERT(SMALLINT,11)
            WHEN SC.NAME = INDEX_COL(@P_TABLE_NAME, SI.INDID, 12) THEN CONVERT(SMALLINT,12)
            WHEN SC.NAME = INDEX_COL(@P_TABLE_NAME, SI.INDID, 13) THEN CONVERT(SMALLINT,13)
            WHEN SC.NAME = INDEX_COL(@P_TABLE_NAME, SI.INDID, 14) THEN CONVERT(SMALLINT,14)
            WHEN SC.NAME = INDEX_COL(@P_TABLE_NAME, SI.INDID, 15) THEN CONVERT(SMALLINT,15)
            WHEN SC.NAME = INDEX_COL(@P_TABLE_NAME, SI.INDID, 16) THEN CONVERT(SMALLINT,16)
            END AS INDEX_SEQ,
       CASE WHEN INDEXKEY_PROPERTY(SO.ID, SI.INDID, SC.COLID, 'IsDescending') = 1
            THEN '1'
            ELSE '0'
            END AS IS_DESCENDING,
       CASE WHEN (SI.STATUS & 0x800) = 0x800
            THEN '1'
            ELSE '0'
            END AS IS_PK,
       CASE WHEN SI.INDID = 1
            THEN '0'
            ELSE '1'
            END AS IS_NONCLUSTERED
  FROM SYSOBJECTS SO INNER JOIN SYSCOLUMNS SC ON SC.ID = SO.ID
                     INNER JOIN SYSINDEXES SI ON SI.ID = SO.ID
 WHERE SO.ID = OBJECT_ID(@P_TABLE_NAME, 'U')
   AND (SI.STATUS & 64) = 0 
   AND (SC.NAME = INDEX_COL(@P_TABLE_NAME, SI.INDID,  1) OR
        SC.NAME = INDEX_COL(@P_TABLE_NAME, SI.INDID,  2) OR
        SC.NAME = INDEX_COL(@P_TABLE_NAME, SI.INDID,  3) OR
        SC.NAME = INDEX_COL(@P_TABLE_NAME, SI.INDID,  4) OR
        SC.NAME = INDEX_COL(@P_TABLE_NAME, SI.INDID,  5) OR
        SC.NAME = INDEX_COL(@P_TABLE_NAME, SI.INDID,  6) OR
        SC.NAME = INDEX_COL(@P_TABLE_NAME, SI.INDID,  7) OR
        SC.NAME = INDEX_COL(@P_TABLE_NAME, SI.INDID,  8) OR
        SC.NAME = INDEX_COL(@P_TABLE_NAME, SI.INDID,  9) OR
        SC.NAME = INDEX_COL(@P_TABLE_NAME, SI.INDID, 10) OR
        SC.NAME = INDEX_COL(@P_TABLE_NAME, SI.INDID, 11) OR
        SC.NAME = INDEX_COL(@P_TABLE_NAME, SI.INDID, 12) OR
        SC.NAME = INDEX_COL(@P_TABLE_NAME, SI.INDID, 13) OR
        SC.NAME = INDEX_COL(@P_TABLE_NAME, SI.INDID, 14) OR
        SC.NAME = INDEX_COL(@P_TABLE_NAME, SI.INDID, 15) OR
        SC.NAME = INDEX_COL(@P_TABLE_NAME, SI.INDID, 16) )
 ORDER BY SI.INDID, INDEX_NM, INDEX_SEQ;

오랜만에 포스팅인데요. 뭐 좀 하다가 딱히 메모해 놓을 곳이 없어 블로그에 기록합니다.

테이블 명을 받아서 해당 테이블의 인덱스를 얻는 쿼리 입니다.

예전에 2000 서버로 개발시에 만들었던 거 조금 다듬었습니다. 그래서, MSSQL 2000 호환 됩니다. 하지만, MSDN 보시면 2005 버전 이후로는 지원하지 않을 예정인 INDEXKEY_PROPERTY 함수를 사용하지 말라 나와 있습니다. 2005 버전 이상에서는 sys.index_columns 인가 하는 시스템 테이블에서 보다 쉽게 인덱스 정보를 얻을 수 있습니다.

각 컬럼 값의 의미.

INDEX_NM : 인덱스 이름 (Index Name)
COL_NM : 컬럼 이름 (Column Name)
INDEX_SEQ : 인덱스 순서 (Index Sequence)
IS_DESCENDING : 정렬 방식 (Ascending, Descending order)
IS_PK : 주 키 여부 (Primary Key)
IS_NONCLUSTERED : 넌클러스터드 여부 (Non-Clustered Index)