There are several methods to find indexes on a table. The methods include using system stored procedure sp_helpindex, system catalog views like sys.indexes or sys.index_columns. We will see these methods one by one.
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]]]');
- Querying the SQL Server system catalog FAQ at MSDN.