How to find similar index in SQL Server Query

This query help to find duplicate index by column name.

---Similar indexes
SELECT s.Name + N'.' + t.name AS [Table]
	,i1.index_id AS [Index1 ID]
	,i1.name AS [Index1 Name]
	,dupIdx.index_id AS [Index2 ID]
	,dupIdx.name AS [Index2 Name]
	,c.name AS [Column]
FROM sys.tables t
JOIN sys.indexes i1 ON t.object_id = i1.object_id
JOIN sys.index_columns ic1 ON ic1.object_id = i1.object_id
	AND ic1.index_id = i1.index_id
	AND ic1.index_column_id = 1
JOIN sys.columns c ON c.object_id = ic1.object_id
	AND c.column_id = ic1.column_id
JOIN sys.schemas s ON t.schema_id = s.schema_id
CROSS APPLY (
	SELECT i2.index_id
		,i2.name
	FROM sys.indexes i2
	JOIN sys.index_columns ic2 ON ic2.object_id = i2.object_id
		AND ic2.index_id = i2.index_id
		AND ic2.index_column_id = 1
	WHERE i2.object_id = i1.object_id
		AND i2.index_id > i1.index_id
		AND ic2.column_id = ic1.column_id
	) dupIdx
ORDER BY s.name
	,t.name
	,i1.index_id
Spread the love

Leave a Comment