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. 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 |
Tech Blog >