SQL server查找指定表的所有索引

时间:2024-01-16 17:07:38
WITH    tmp
AS ( SELECT indexname = a.name ,
tablename = c.name ,
indexcolumns = d.name ,
a.indid
FROM sysindexes a
JOIN sysindexkeys b ON a.id = b.id
AND a.indid = b.indid
JOIN sysobjects c ON b.id = c.id
JOIN syscolumns d ON b.id = d.id
AND b.colid = d.colid
WHERE a.indid NOT IN ( 0, 255 )
-- and c.xtype='U' and c.status>0 -- 查所有用户表
AND c.name = 'T_Houses' --查指定表
)
SELECT tmp.indexname '索引名称' ,
tmp.tablename '表名' ,
( SELECT A.indexcolumns + ','
FROM tmp A
WHERE A.indexname = tmp.indexname
AND A.indid = tmp.indid
FOR
XML PATH('')
) AS '索引列' ,
tmp.indid '索引id'
FROM tmp
GROUP BY tmp.indexname ,
tmp.tablename ,
tmp.indid

SQL server查找指定表的所有索引