查询所有索引

select indexs.Tab_Name as [表名],indexs.Index_Name as [索引名] ,indexs.[Co_Names] as [索引列],
Ind_Attribute.is_primary_key as [是否主键],Ind_Attribute.is_unique AS [是否唯一键],
Ind_Attribute.is_disabled AS [是否禁用]
from (
select Tab_Name,Index_Name, [Co_Names]=stuff((select '，'+[Co_Name] from
( select tab.Name as Tab_Name,ind.Name as Index_Name,Col.Name as Co_Name from sys.indexes ind
inner join sys.tables tab on ind.Object_id = tab.object_id and ind.type in (1,2)
inner join sys.index_columns index_columns on tab.object_id = index_columns.object_id and ind.index_id = index_columns.index_id
inner join sys.columns Col on tab.object_id = Col.object_id and index_columns.column_id = Col.column_id
) t where Tab_Name=tb.Tab_Name and Index_Name=tb.Index_Name for xml path('')), 1, 1, '')
from (
select tab.Name as Tab_Name,ind.Name as Index_Name,Col.Name as Co_Name from sys.indexes ind
inner join sys.tables tab on ind.Object_id = tab.object_id and ind.type in (1,2)
inner join sys.index_columns index_columns on tab.object_id = index_columns.object_id and ind.index_id = index_columns.index_id
inner join sys.columns Col on tab.object_id = Col.object_id and index_columns.column_id = Col.column_id
)tb
where Tab_Name not like 'sys%'
group by Tab_Name,Index_Name
) indexs inner join sys.indexes Ind_Attribute on indexs.Index_Name = Ind_Attribute.name
order by indexs.Tab_Name

转载于:https://www.cnblogs.com/songguangjin/p/9176516.html