Tech Blog‎ > ‎

TSQL - Get Index By Table Name + Column Name, Skip Composite Indices

posted Sep 24, 2017, 6:14 PM by Victor Zakharov   [ updated Sep 24, 2017, 6:15 PM ]
Below SQL code will find an index name that corresponds to the given table name and column name pair, and only include those indices with one column (exclude composite indices). Indices can be changed / added / removed by a DBA without impact to the application (part of DB tuning), so it's best not to assume their naming in an application deployment script. This can be useful to remove / replace certain indices without relying on index name.

Based on this Stack Overflow's answer:
- List of all index & index columns in SQL Server DB 
DECLARE @indexName VARCHAR(256)

WITH indices AS (
    SELECT TableName = MAX(t.name),
           ColumnName = MAX(c.name),
           IndexName = i.name,
           COUNT(*) as FieldCount
      FROM sys.indexes i
      JOIN sys.index_columns ic
        ON i.object_id = ic.object_id and i.index_id = ic.index_id
      JOIN sys.columns c
        ON ic.object_id = c.object_id and ic.column_id = c.column_id
      JOIN sys.tables t
        ON i.object_id = t.object_id 
  GROUP BY i.name
)

SELECT @indexName = i.IndexName FROM indices i
WHERE i.TableName = 'Person' --table name filter  
  AND i.ColumnName = 'FirstName' --column name filter
  AND i.FieldCount = 1 --index only contains this column, not a composite index 

SELECT @indexName