查询语句如下：
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
