How to find Indexes on a Table In SQL Server

Using system stored procedure sp_helpindex, system catalog views like sys.indexes or sys.index_columns methods. 

1. Find Indexes On A Table Using SP_HELPINDEX

sp_helpindex is a system stored procedure which lists the information of all the indexes on a table or view. This is the easiest method to find the indexes in a table. sp_helpindex returns the name of the index, description of the index and the name of the column on which the index was created.

EXEC sp_helpindex '[[[SCHEMA-NAME.TABLE-NAME]]]'
GO


2. Using SYS.INDEXES

The sys.indexes system catalog view returns all the indexes of the table or view or table valued function. If you want to list down the indexes on a table alone, then you can filter the view using the object_id of the table. Here is the syntax for using the sys.indexes view to list the indexes of a table. In this example, I’ve filtered out the hypothetical index using the WHERE clause condition is_hypothetical = 0. If you think you need to see the hypothetical index, then ignore this condition

SELECT
    name AS Index_Name,
    type_desc  As Index_Type,
    is_unique,
    OBJECT_NAME(object_id) As Table_Name
FROM  sys.indexes
WHERE
    is_hypothetical = 0 AND
    index_id != 0 AND
    object_id = OBJECT_ID('[[[SCHEMA-NAME.TABLE-NAME]]]');  

GO 

3. Using SYS.INDEX_COLUMNS

This method is an extension of the earlier sys.indexes method. Here we are joining another system catalog view, sys.index_columns to get the name of the column or columns the index was created or included. This will be helpful to see the column names along with the index name.

SELECT
    a.name AS Index_Name,
    OBJECT_NAME(a.object_id),
    COL_NAME(b.object_id,b.column_id) AS Column_Name,
    b.index_column_id,
    b.key_ordinal,
    b.is_included_column  
FROM
    sys.indexes AS a  
INNER JOIN
    sys.index_columns AS b   
       ON a.object_id = b.object_id AND a.index_id = b.index_id  
WHERE
        a.is_hypothetical = 0 AND

    a.object_id = OBJECT_ID('[[[SCHEMA-NAME.TABLE-NAME]]]'); 
GO

..Shyamb4u