查询列出同一表下引用同一列的所有索引

Question

我想查询，这将有助于我找到所有的表有列索引两次。我已经找到了一个语句，它列出了所有索引及其各自的列和类型，但我无法修改它来实现我的目标:

select i.[name] as index_name,
substring(column_names, 1, len(column_names)-1) as [columns],
case when i.[type] = 1 then 'Clustered index'
when i.[type] = 2 then 'Nonclustered unique index'
when i.[type] = 3 then 'XML index'
when i.[type] = 4 then 'Spatial index'
when i.[type] = 5 then 'Clustered columnstore index'
when i.[type] = 6 then 'Nonclustered columnstore index'
when i.[type] = 7 then 'Nonclustered hash index'
end as index_type,
case when i.is_unique = 1 then 'Unique'
else 'Not unique' end as [unique],
schema_name(t.schema_id) + '.' + t.[name] as table_view, 
case when t.[type] = 'U' then 'Table'
when t.[type] = 'V' then 'View'
end as [object_type]
from sys.objects t
inner join sys.indexes i
on t.object_id = i.object_id
cross apply (select col.[name] + ', '
from sys.index_columns ic
inner join sys.columns col
on ic.object_id = col.object_id
and ic.column_id = col.column_id
where ic.object_id = t.object_id
and ic.index_id = i.index_id
order by key_ordinal
for xml path ('') ) D (column_names)
where t.is_ms_shipped <> 1
and index_id > 0

谢谢!

Answer 1

如果您正在查看过于简化的方法，如果一个COLUMN存在于一个INDEX中，那么它不需要存在于另一个INDEX中，那么您的方法就是错误的。

如果您将此作为起点，查看是否在多个索引中使用了列，然后进一步分析索引以查看是否可以合并或删除其中的一些，那么这可能是有效的。

我建议简化初始部分以获得相关的INDEX COLUMNS。以:

开头

SELECT object_id, column_id, COUNT(*)
FROM sys.index_columns 
GROUP BY object_id, column_id
HAVING COUNT(*) > 1 

这将识别所有COLUMN存在不止一次的INDEX和COLUMN组合。使用它作为派生表，并将其他表连接到它以获得实际的对象，索引和列名。