How to know what Indexes are on a table and columns.

Problem: How to know what all indexes are present on a table and Indexed on specific columns .

Solution: sys.indexes, sys.index_columns

This two object Catalog views bring us information related to indexes and the columns which they have in the indexes.

Example:

I am going to show you where indexes exist on a table and let’s create a few indexes and then search for them with the help of the above two views. Here we go.

I have created two indexes and this is how it looks from the object explorer.

Please see the script below.

SELECT

                 SchemaName=schema_name(t.schema_id),

     TableName = t.name,

     IndexName = i.name,

     IndexId = i.index_id,

     ColumnId = ic.index_column_id,

     ColumnName = c.name,

                 Index_type = i.type_desc

FROM

     sys.indexes i

INNER JOIN

     sys.index_columns ic ON  i.object_id = ic.object_id and i.index_id = ic.index_id

INNER JOIN

     sys.columns c ON ic.object_id = c.object_id and ic.column_id = c.column_id

INNER JOIN

     sys.tables t ON i.object_id = t.object_id

WHERE

     i.is_primary_key = 0

     AND i.is_unique = 0

     AND i.is_unique_constraint = 0

     AND t.is_ms_shipped = 0

                 AND schema_name(t. schema_id)='dbo'

ORDER BY

     t.name, i.name, i.index_id, ic.index_column_id

OUTPUT: