SQL Server 2012 查询数据库中所有表的索引信息

查询语句如下：

with temp as(
    select  a.name as tabname,a.object_id
    ,h.name as indexName,h.index_id,ic.column_id,c.name colName
    from  sys.objects    as  a
    right join sys.indexes  as h  on  a.object_id=h.object_id
    left join sys.index_columns ic on h.index_id=ic.index_id and ic.object_id=a.object_id
    left join sys.columns c on ic.column_id=c.column_id and c.object_id=a.object_id
    where  a.type<>'s' and a.type='U'
    --and isnull(h.name,'')='' --没有索引的表
    and a.is_ms_shipped<>1 --排除 dtproperties
)
 
select distinct temp.tabname,indexName
,stuff((
        select ',' + tc.name from sys.columns tc
        left join sys.index_columns ic on ic.column_id=tc.column_id and ic.object_id=temp.object_id
        where tc.object_id=temp.object_id and ic.index_id=temp.index_id and tc.column_id in (
            select column_id from temp where object_id=tc.object_id and ic.index_id=temp.index_id
            ) for xml path('')
    ),1,1,''
) columnName
from temp
order by temp.indexName