Find Indexes On A Table In SQL Server

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.

Syntax

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

Example

SQL Server Find Indexes On A Table 02

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

Syntax

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

Example

SQL Server Find Indexes On A Table 01

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.

Syntax

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]]]');

Example

SQL Server Find Indexes On A Table 03

Related Articles

Reference

  • Querying the SQL Server system catalog FAQ at MSDN.


Leave your thoughts...

This site uses Akismet to reduce spam. Learn how your comment data is processed.