List indexes in SQL Server database with a 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.

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 grouping 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.

Display the modification date of a SQL Server table