MS SQL INDEX LIST 取得現有 MS SQL 的資料表的index清單

'取得現有 MS SQL 的資料表的index清單

SELECT TBL.NAME AS TABLE_NAME,
        I.NAME AS INDEX_NAME,
        I.INDEX_ID,
        (CASE IC.IS_INCLUDED_COLUMN
            WHEN 1 THEN IC.INDEX_COLUMN_ID
            ELSE IC.KEY_ORDINAL
         END)
           AS [ID],
        CLMNS.NAME AS COLUMN_NAME,
        CLMNS.COLUMN_ID AS COLUMN_POSITION,
        IC.IS_DESCENDING_KEY AS DESCEND , I.IS_UNIQUE
   FROM          SYS.TABLES AS TBL
              INNER JOIN
                 SYS.INDEXES AS I
              ON (I.OBJECT_ID = TBL.OBJECT_ID)
           INNER JOIN
              SYS.INDEX_COLUMNS AS IC
           ON (IC.COLUMN_ID > 0
               AND (   IC.KEY_ORDINAL > 0
                    OR IC.PARTITION_ORDINAL = 0
                    OR IC.IS_INCLUDED_COLUMN != 0))
              AND (IC.INDEX_ID = CAST (I.INDEX_ID AS INT)
                   AND IC.OBJECT_ID = I.OBJECT_ID)
        INNER JOIN
           SYS.COLUMNS AS CLMNS
        ON CLMNS.OBJECT_ID = IC.OBJECT_ID AND CLMNS.COLUMN_ID = IC.COLUMN_ID
  WHERE 1 = 1
 --(I.NAME='???')
 --AND (TBL.NAME=???'  )
 ORDER BY TBL.NAME , INDEX_ID ASC , [ID]