SQL SERVER获取某张表创建的索引

--方案一
SELECT 
  表名 = c.name,
  索引类型 = CASE a.indid WHEN 1 THEN '聚集索引' ELSE '非聚集索引' END,
  索引名称 = ISNULL(a.name, '无索引名'),
  索引字段名 = d.name,
  索引字段位置 = b.keyno,
  字段类型 = CASE WHEN b.keyno > 0 THEN '索引键列' ELSE '包含性列(INCLUDE)' END
FROM sysindexes a
INNER JOIN sysindexkeys b ON a.id = b.id AND a.indid = b.indid
INNER JOIN sysobjects c ON a.id = c.id 
INNER JOIN syscolumns d ON a.id = d.id AND b.colid = d.colid
WHERE a.indid NOT IN (0,255)  -- 排除堆表、系统隐藏索引
  AND c.xtype = 'U'           -- 只查用户业务表，排除系统表/视图
AND c.name='tableName' --你的表名称
ORDER BY c.name,a.name,d.name ASC

--方案二
WITH tbl
AS (
    SELECT A.index_id, A.object_id, B.name
    FROM   sys.index_columns A
    INNER JOIN sys.columns B ON A.column_id=B.column_id AND A.object_id=B.object_id
)

SELECT A.name AS TableName, B.name AS IndexName, B.rows, C.columnname AS ColumnName
FROM sys.tables AS A
INNER JOIN sysindexes B ON A.object_id=B.id
INNER JOIN (
    SELECT   index_id, object_id, (
    SELECT name+','
    FROM   tbl
    WHERE  tbl.index_id=A.index_id AND tbl.object_id=A.object_id
    FOR XML PATH('')
    ) AS columnname
    FROM     tbl A
    GROUP BY index_id, object_id
) C ON A.object_id=C.object_id AND B.indid=C.index_id
WHERE    B.groupid=1
--查询某个表的索引
AND A.name='表名称'
--包含某个字段名
and  C.columnname  like '%字段名%'
ORDER BY B.rows DESC,A.name,b.name,b.indid ASC