這段時間維護一個系統(tǒng)，對SQL做優(yōu)化，經(jīng)常會去查看一個表的索引信息

以前我常用 sp_helpindex 表名 來查看，但結(jié)果少了重要的include列信息

用sp_help 表名 一樣也缺少 include列信息

然后每次都需要從系統(tǒng)表中查詢，且查詢出來是列排序的，不方便查看
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

如上圖，看起來舒服多了。