반응형
/* [MSSQL] 인덱스 조회 */
SELECT A.NAME AS TBL_ID
, B.TYPE
, B.NAME AS IDX_ID
, C.KEY_ORDINAL
, D.NAME AS COL_ID
, C.IS_DESCENDING_KEY
/*, C.IS_INCLUDED_COLUMN
, B.INDEX_ID
, B.TYPE_DESC
, B.IS_PRIMARY_KEY
, B.IS_UNIQUE_CONSTRAINT
, B.IS_DISABLED
, B.IS_HYPOTHETICAL
, C.INDEX_COLUMN_ID
, C.COLUMN_ID*/
FROM SYS.TABLES A
INNER JOIN SYS.INDEXES B
ON A.OBJECT_ID = B.OBJECT_ID
AND B.TYPE IN ('1','2')
LEFT JOIN SYS.INDEX_COLUMNS C
ON B.OBJECT_ID = C.OBJECT_ID
AND B.INDEX_ID = C.INDEX_ID
LEFT JOIN SYS.COLUMNS D
ON C.OBJECT_ID = D.OBJECT_ID
AND C.COLUMN_ID = D.COLUMN_ID
ORDER BY 1,2,3,4
;