How to list all SQL Server indexes from database with one query ?

How to list indexes in SQL Server database with one SQL query ? It can be very useful to display all database indexes. Indeed, to have an idea immediately at how this SQL Server database is optimized with indexes, whether an index is A clustered or a non-clustered one.

Indeed, displaying all indexes in an SQL Server database using a single query can provide valuable insights into the database’s performance and optimization. Indexes play a crucial role in enhancing query performance by minimizing the amount of data that needs to be read, thus reducing the overall response time.

Why it is useful to list all MS SQL indexes at once ?

In various use cases, it is essential to monitor and evaluate the indexes to ensure optimal database performance. Some of these use cases include:

1. Identifying missing indexes: By reviewing the existing indexes, database administrators can identify potential areas for improvement, such as adding missing indexes to optimize query performance further.
2. Detecting redundant indexes: In some cases, there might be redundant or duplicate indexes that consume unnecessary resources and storage space. Displaying all indexes can help identify these redundancies, allowing for their removal to improve overall efficiency.
3. Evaluating index usage: Understanding how frequently each index is used can help in determining their effectiveness. If an index is rarely used, it may be worth considering its removal to save resources.
4. Analyzing index fragmentation: Over time, indexes can become fragmented, leading to decreased performance. By displaying all indexes, administrators can identify fragmented indexes that require maintenance or reorganization.
5. Assessing index size: Reviewing index sizes can help in identifying large indexes that might be impacting the database’s performance, and prompt further investigation into their necessity or potential for optimization.

T-SQL query to list all indexes in a SQL Server database

This query offers you a synthetic list of indexes. If shows for example if the column is a primary key if it’s unique and the type of the index used (clustered or non-clustered). Build the query in 3 steps.

1. First, use a CTE (Common Table Expression) to prepare the data. The data comes from different tables in the system such as indexes, columns, and tables.
2. Then, call the CTE using the XML FOR XML PATH function. The XML function allows you to retrieve and list the columns used in the index on the same line. It is possible to use another way.
3. And finally group the data with a GROUP BY.

Here is how to manage text variables with more than 8000 characters in T-SQL.

-- Building the CTE from system tables: [sys].[indexes], [sys].[columns] and [sys].[tables]

WITH LIST_INDEXS
AS (
SELECT
	sysidxcol.[index_id] + sysidxcol.[object_id] AS [IndexId],
	l_t.[name] AS [Table],
	sysidx.[name] AS [Index],
	li.[name] AS [Column],
	sysidx.[type_desc] AS [Description],
	sysidx.[is_primary_key] AS [PrimaryKey],
	sysidx.[is_unique] AS [Unique]

FROM [sys].[indexes] sysidx
	INNER JOIN [sys].[index_columns] sysidxcol
		ON 	sysidx.[index_id] = sysidxcol.[index_id]
		AND sysidx.[object_id] = sysidxcol.[object_id]
	INNER JOIN [sys].[columns] li
		ON sysidxcol.[column_id] = li.[column_id]
		AND sysidx.[object_id] = li.[object_id]
	INNER JOIN [sys].[tables] l_t
		ON sysidx.[object_id] = l_t.[object_id]
)

-- Selection of the needed informations
SELECT li.[Table],
	li.[Index],
	li.[Description],
	li.[PrimaryKey],
	li.[Unique],
	STUFF((SELECT ',' + a.[Column]
	FROM LISTE_INDEXS a
	WHERE li.[IndexId] = a.[IndexId]
	FOR XML PATH('')), 1, 1, '') AS [XMLColumns]
FROM LIST_INDEXS li
GROUP BY li.[IndexId],
	li.[Table],
	li.[Index],
	li.[Description],
	li.[PrimaryKey],
	li.[Unique]
ORDER BY 
	li.[Table] ASC,
	li.[PrimaryKey] DESC;

Check out this other way of listing all indexes from stackoverflow. To conclude this query displays the indexes of the current database. Check out this section of the blog is about the DAX functions. To go further, here is a SQL query to display the modification date of a SQL Server table.

Modification date of SQL Server tables and views with a T-SQL query