How to list all SQL Server indexes?

How to list all SQL Server indexes? It can be very useful to display all database indexes. Indeed, to have an idea at a glance 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).

How to list all SQL Server indexes?

This query is build in two steps. First of all it uses a CTE to prepare the data. Data is from the different system tables like indexes, columns and tables.

Secondly it calls the CTE using the XML function.

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

To conclude this query displays the indexes of the current database. Check out this section of the blog is about the DAX functions.