Query to list all indexes in a SQL Server database

Whenever I’m analysing the performance of a SQL Server database, I often find myself needing to retrieve a list of all indexes for review.

Missing or unsuitable indexes are a leading cause of SQL Server slowdowns, so it can be very useful to view the existing indexes to get an idea of how heavily indexed or not a particular database currently is.

Having a SQL query that can display the details of all indexes, such as the index name and index columns, along with other helpful information such as the related table or view name can be an invaluable resource.

In this article, I am going to look at such a query and show an example of the kind of results that it produces.

The Query

Let’s jump straight into the query which I’ve included in the sub-section below.

The query source is also included in my SQL Server Scripts GitHub repository, along with other useful SQL scripts.

SQL

The SQL query has been adapted to display the most important information in what I believe is the most relevant order.

-- Example of how to list all indexes in a database.
SELECT 
    SCHEMA_NAME(o.[schema_id]) + '.' + o.[name] AS [Table/View Name],
    i.[name] AS [Index Name],
    SUBSTRING(key_column_names, 1, LEN(key_column_names) - 1) AS [Index Key Columns],
    ISNULL(SUBSTRING(included_column_names, 1, LEN(included_column_names) - 1), '') AS [Included Columns],
    CASE WHEN i.[type] = 1 THEN 'Clustered Index'
         WHEN i.[type] = 2 THEN 'Non-Clustered Unique Index'
         WHEN i.[type] = 3 THEN 'XML Index'
         WHEN i.[type] = 4 THEN 'Spatial Index'
         WHEN i.[type] = 5 THEN 'Clustered Columnstore Index'
         WHEN i.[type] = 6 THEN 'Non-Clustered Columnstore Index'
         WHEN i.[type] = 7 THEN 'Non-Clustered Hash Index'
         END AS [Index Type],
    CASE WHEN i.is_unique = 1 THEN 'Yes'
         ELSE 'No'
	 END AS [Unique],
    CASE WHEN o.[type] = 'U' THEN 'Table'
         WHEN o.[type] = 'V' THEN 'View'
         END AS [Object Type]
FROM sys.objects o
    INNER JOIN sys.indexes i ON o.[object_id] = i.[object_id]
    CROSS APPLY(
	SELECT c.[name] + ', '
        FROM sys.index_columns ic
        INNER JOIN sys.columns c ON ic.[object_id] = c.[object_id] AND ic.column_id = c.column_id
        WHERE ic.[object_id] = o.[object_id] AND ic.index_id = i.index_id AND ic.is_included_column = 0
        order by key_ordinal
        FOR XML PATH ('')) A (key_column_names)
    CROSS APPLY(
	SELECT c.[name] + ', '
        FROM sys.index_columns ic
        INNER JOIN sys.columns c ON ic.[object_id] = c.[object_id] AND ic.column_id = c.column_id
        WHERE ic.[object_id] = o.[object_id] AND ic.index_id = i.index_id AND ic.is_included_column = 1
        ORDER BY index_column_id
        FOR XML PATH ('')) B (included_column_names)
WHERE o.is_ms_shipped <> 1 AND i.index_id > 0 AND i.is_primary_key = 0
ORDER BY o.[name], i.[name]

A fair bit is going on in this query, so I’ll explain it further in the following sub-section.

Breakdown

Let’s break down the details of what is returned by the above query.

Table/View Name

The first column returned is the name of the table or view that the index is associated with. The schema and name of the table/view are selected from the sys.objects table.

I find that having this column listed first is best for the scenarios I most commonly use the query for. Usually, I’m trying to identify if the indexes that I’m expecting to exist on certain tables are already there, or if I need to consider dropping existing indexes on a specific table and craft some better ones.

Index Name

The name of the index is selected from the sys.indexes table. Of course, this column is very important for identifying each index that is returned. I find that the index name naturally sits well alongside the table/view name for context.

Index Key Columns

The columns that make up the index key are displayed next in a comma-separated fashion. The name of each column is selected from the sys.columns table which is joined to the sys.index_columns table within the CROSS APPLY near the end of the query. The key columns are identified by filtering on the is_included_column column where the value is 0.

Included Columns

In the same manner as the index key columns, the included columns are displayed in comma-separated format. The sub-query within the CROSS APPLY is almost identical to the sub-query for the index key columns, except that the filtering on the is_included_column column is changed to filter where the value is 1.

Index Type

It can be useful to know the type of the index. The index type is determined by selecting it from the sys.indexes table. In most cases, this will either be ‘Clustered Index’ or ‘Non-Clustered Unique Index’.

Unique

It can also be useful to know whether the index has been configured as a unique index. This is determined in the query by returning either ‘Yes’ or ‘No’ depending on the value of the is_unique column on the sys.indexes table.

Object Type

If you have lots of views in your database (in addition to tables) it can be helpful to see what type of object the index is associated with. If this is the case you may find it more useful to see the object type as one of the first columns in the query rather than being the last. The type of object is displayed as either ‘Table’ or ‘View’ depending on the value of the type column on the sys.objects table.

Filtering and ordering

The WHERE clause near the end of the query excludes Microsoft objects and checks the index ID.

In addition to the above filters, any primary key indexes are excluded. In some scenarios, you may need to see the primary keys, as missing primary keys can cause big performance problems. Feel free to remove this clause if you suspect that this may be the case when analysing your database.

If you are running the query against a replicated database, you may find that the results are cluttered with replication-specific indexes, with names similar to the following.

MSmerge_index_19564342454

In these cases, you can add another condition to the WHERE clause to exclude these. For example, for a database that is set up for Merge Replication, you can add the following.

AND i.name NOT LIKE 'MSMerge_index_%'

Other than the index name prefix (i.e. ‘MSMerge_index_’) there isn’t any other index property we can filter on to exclude replication indexes.

The records returned by the query are ordered first by the table/view name, and then by the index name.

Feel free to adjust the ordering to whatever makes the most sense to you. For example, you could also order by the type of index or simply order on the index name itself.

The Results

After running the query the results should be similar to the following screenshot, albeit with records that are relevant to the tables/views and indexes that are in your particular database.

The above results are from a slightly modified version of the sample AdventureWorks database.

I’ve reduced the width of the columns so that they all fit into the Results pane within SQL Server Management Studio. However, as you can see from the output of the query, the well-named headings and the ordering that has been applied produce a highly readable set of results.

Summary

In this article, I’ve shown you a query that can be used to list all indexes in a SQL Server database.

I’ve walked through the details of what is returned by the query and discussed how the records are filtered and ordered.

Lastly, I’ve demonstrated what the results look like in a sample database.