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: 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.