这段时间维护一个系统，对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

如上图，看起来舒服多了。