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:
SELECT
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
WHERE T.Name='MyTable'
AND C.Name='MyColumn'
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.