By default, SQL Server creates indexes automatically when we do things like create certain constraints. We also have the option of creating indexes separately for our own (usually performance related) reasons. Either way, there may be times where we need to check what indexes we have in our database.

In this article, we’ll explore four ways to retrieve information about all indexes in a SQL Server database.

Use the sys.indexes System View

The simplest way to list all indexes in our database is by querying the sys.indexes system view. Here’s a basic query:

SELECT 
    OBJECT_SCHEMA_NAME(object_id) AS SchemaName,
    OBJECT_NAME(object_id) AS TableName,
    name AS IndexName,
    type_desc AS IndexType
FROM 
    sys.indexes
WHERE 
    OBJECT_SCHEMA_NAME(object_id) <> 'sys' -- Filter out the 'sys' schema
ORDER BY 
    SchemaName, TableName, IndexName;

This query provides us with the schema name, table name, index name, and index type for all indexes in our database that don’t belong to the sys schema.

Including the Index Columns

The sys.indexes view doesn’t include the names of the column/s in each index. To get that information, we can perform a join on multiple system views:

SELECT 
    OBJECT_SCHEMA_NAME(i.object_id) AS SchemaName,
    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_SCHEMA_NAME(object_id) <> 'sys'
ORDER BY 
    SchemaName, TableName, IndexName;

Indexes can contain more than one column and so this query presents them as a comma separated list in the IndexColumns column.

Using the sp_helpindex Stored Procedure

SQL Server provides a built-in stored procedure called sp_helpindex that we can use to get index information for a specific table:

EXEC sp_helpindex 'Sales.OrderItems';

For each index in the table, this returns the index name, description, and the column/s used in the index.

To list indexes for all tables, we can use dynamic SQL:

DECLARE @sql NVARCHAR(MAX) = '';

SELECT @sql += 'EXEC sp_helpindex ''' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) + ''';' + CHAR(10)
FROM sys.tables
WHERE type = 'U'; -- User tables only

EXEC sp_executesql @sql;

Bear in mind that this will output a different result set for each index, so if you have a lot of indexes, this could require a lot of scrolling to review all indexes.

Querying the index_columns System View

If we’re interested in the column order within indexes, we can use the sys.index_columns view:

SELECT 
    OBJECT_SCHEMA_NAME(ic.object_id) AS SchemaName,
    OBJECT_NAME(ic.object_id) AS TableName,
    i.name AS IndexName,
    c.name AS ColumnName,
    ic.key_ordinal,
    ic.is_included_column
FROM 
    sys.index_columns ic
JOIN 
    sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
JOIN 
    sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE 
    OBJECT_SCHEMA_NAME(i.object_id) <> 'sys'
ORDER BY 
    SchemaName, TableName, IndexName, ic.key_ordinal;

This query shows us each column in every index, along with its ordinal position and whether it’s an included column. An “included column” is a non-key column added to the index by using the CREATE INDEX INCLUDE clause, or the column is part of a columnstore index.