Lister les index SQL server avec une requête

4 mai 2022

Comment lister tous les index SQL Server d’une base de données ? Il peut être très utile d’afficher tous les index de la base de données. En effet, pour avoir une idée en un coup d’œil de la façon dont la base de données SQL Server est optimisée avec les index. Et aussi si un index est cluster ou non cluster.

Cette requête vous offre une liste synthétique des index. Elle montre par exemple si la colonne est une clé primaire, si elle est unique et le type d’index utilisé, cluster ou non cluster.

Comment lister tous les index SQL Server avec une requête ?

Construire la requête en deux étapes. Tout d’abord, utiliser un CTE (Common Table Expression) pour préparer les données. Les données proviennent des différentes tables du système comme les index, les colonnes et les tables.

Ensuite, appeler le CTE en utilisant la fonction XML FOR XML PATH. La fonction XML permet de récupérer et lister sur une même ligne les colonnes utilisées dans l’index. Il est possible d’utiliser une autre façon. En effet, voici comment afficher le contenu d’une colonne SQL Server en ligne et avec des virgules.

Et enfin grouper les données avec un GROUP BY.

-- Construire le CTE à partir des trois tables système suivantes : 
-- [sys].[indexes], [sys].[columns] et [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;

Voici une autre façon de répertorier tous les index d’une base de données SQL Server proposée sur stackoverflow.

Pour conclure, cette requête affiche une liste de tous les index d’une base de données SQL Server.

Pour aller plus loin, voici cette fois comment lister toutes les tables d’une base MS SQL.