这段时间维护一个系统，对SQL做优化，经常会去查看一个表的索引信息
以前我常用 sp_helpindex 表名 来查看，但结果少了重要的include列信息
用sp_help 表名 一样也缺少 include列信息
然后每次都需要从系统表中查询，且查询出来是列排序的，不方便查看
SQL如下：
SELECT i.name AS index_name
,COL_NAME(ic.object_id,ic.column_id) AS column_name
,ic.index_column_id
,ic.key_ordinal
,ic.is_included_column
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
ON i.object_id = ic.object_id AND i.index_id = ic.index_id
WHERE i.object_id = OBJECT_ID('Production.BillOfMaterials');
我做了一下改进
SELECT i.name AS index_name,is_unique,is_primary_key,
stuff((select ',' + COL_NAME(t.object_id,t.column_id)
from sys.index_columns as t where i.object_id = t.object_id and i.index_id = t.index_id
and t.is_included_column = 0 order by key_ordinal for xml path('')),1,1,'') as indexcols,
stuff((select ',' + COL_NAME(t.object_id,t.column_id)
from sys.index_columns as t where i.object_id = t.object_id and i.index_id = t.index_id
and t.is_included_column = 1 order by key_ordinal for xml path('')),1,1,'') as includecols
FROM sys.indexes AS i
WHERE i.object_id = OBJECT_ID('goods')
group by i.name,i.object_id,i.index_id,is_unique,is_primary_key
ccdd.png
如上图，看起来舒服多了。
