This SQL query helps identify duplicate indexes based on a specific column name. It retrieves details about indexes that are similar to each other in terms of the column they index.
SQL Query to Find Duplicate Indexes by Column Name
The following query can be used to find duplicate indexes in your database. The duplicates are determined by comparing the columns used in different indexes within the same table.
-- Query to identify similar indexes
SELECT
s.Name + N'.' + t.name AS [Table], -- Table name
i1.index_id AS [Index1 ID], -- First index ID
i1.name AS [Index1 Name], -- First index name
dupIdx.index_id AS [Index2 ID], -- Duplicate index ID
dupIdx.name AS [Index2 Name], -- Duplicate index name
c.name AS [Column] -- Column name
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, -- Duplicate index ID
i2.name -- Duplicate index 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; -- Sorting by schema, table, and index ID
Make sure to customize this query based on your database structure and indexing strategy for optimal results.
