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.

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

List all the indexes in a SQL Server database with a T-SQL query

Build the query in two steps. 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.

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.

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

And finally grouping the data with a GROUP BY.

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

Be the first to comment

Leave a Reply

Your email address will not be published.


*