Cry How to...
List all indexes which use a specific table column
If you need to see which indexes on a table make use of a given column then use:
T.Name as TableName,
I.Name as IndexName,
C.Name as ColumnName
FROM sys.tables T
INNER JOIN sys.indexes I ON I.object_id = T.object_id
INNER JOIN sys.index_columns IC ON IC.object_id = T.object_id and IC.index_id = I.index_id
INNER JOIN sys.columns C ON C.object_id = T.object_id AND C.column_id = IC.column_id
substuting the table name for
MyTable and the column name for
MyColumn in the above.
This query lists the name of the table, the name of the index and the name of the column.
These notes have been tested against SQL Server 2008 R2, SQL Server 2008.
About the author: Brian Cryer is a dedicated software developer and webmaster. For his day job he develops websites and desktop applications as well as providing IT services. He moonlights as a technical author and consultant.