How to list all SQL Server indexes?

How to list all SQL Server indexes? It can be very useful to display all database indexes to have an idea at a glance at how this SQL Server database is optimized with indexes, whether CLUSTERED or NON CLUSTERED.

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?

-- Building the CTE from system tables: [sys].[indexes], [sys].[columns] and [sys].[tables]
WITH LISTE_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 LISTE_INDEXS li
GROUP BY li.[IndexId],
li.[Table],
li.[Index],
li.[Description],
li.[PrimaryKey],
li.[Unique]
ORDER BY li.[Table] ASC,
li.[PrimaryKey] DESC

To conclude this query displays the indexes of the current database.

Check out this section of the blog is about the DAX functions.