Sometimes when working with SQL Server, we need to get a quick list of indexes on a given table.

Here are three queries that return all indexes on a given table in SQL Server.

Option 1: sp_helpindex

We can use the sp_helpindex stored procedure to return information about indexes on a given table:

sp_helpindex 'Employees'

Example result:

index_name                      index_description                                  index_keys         
------------------------------  -------------------------------------------------  -------------------
IX_Employees_Department         nonclustered located on PRIMARY                    Department         
IX_Employees_Name               nonclustered located on PRIMARY                    LastName, FirstName
PK__Employee__7AD04FF11150B041  clustered, unique, primary key located on PRIMARY  EmployeeID         

Option 2: sys.indexes

This query checks the sys.indexes system catalog view for all indexes on a given table:

SELECT
    name,
    type_desc,
    is_primary_key,
    is_disabled
FROM 
    sys.indexes
WHERE 
    object_id = OBJECT_ID('dbo.Employees');

Example result:

name                            type_desc     is_primary_key  is_disabled
------------------------------  ------------  --------------  -----------
PK__Employee__7AD04FF11150B041  CLUSTERED     true            false      
IX_Employees_Department         NONCLUSTERED  false           false      
IX_Employees_Name               NONCLUSTERED  false           false      

Just replace the table name with your own table name (including the schema name).

This query selects just a subset of the columns from the view. As with any query, you can use the asterisk wildcard (*) to select all columns if required.

Option 3: Add Columns

This query includes the columns that are included in each index. We do this by joining the sys.indexes view with the sys.columns view:

SELECT 
    OBJECT_NAME(i.object_id) AS TableName,
    i.name AS IndexName,
    i.type_desc AS IndexType,
    STUFF((
        SELECT ', ' + c.name
        FROM sys.index_columns ic
        JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
        WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id
        ORDER BY ic.key_ordinal
        FOR XML PATH('')
    ), 1, 2, '') AS IndexColumns
FROM 
    sys.indexes i
WHERE object_id = OBJECT_ID('dbo.Employees')
ORDER BY 
    TableName, IndexName;

Example result:

TableName  IndexName                       IndexType     IndexColumns       
---------  ------------------------------  ------------  -------------------
Employees  IX_Employees_Department         NONCLUSTERED  Department         
Employees  IX_Employees_Name               NONCLUSTERED  LastName, FirstName
Employees  PK__Employee__7AD04FF11150B041  CLUSTERED     EmployeeID         

Again, replace the table name with your own table name (including the schema name).