Dans la série des petites scripts utiles, voici une instruction simple permettant d’identifier les contraintes de clés étrangères pour lesquelles il manque un index dans la table parente. Un tel manque d’index peut parfois provoquer des temps de réponse extrêmement important pour de simples suppressions unitaires d’enregistrements dans une table de référence.

Voici donc un script permettant de lister l’ensemble des clés étrangères pour lesquelles un index d’appui manque, ainsi que les tables concernées par ces clés.

-- Liste des clés étrangères avec index absent
-- Auteur : Jean-Nicolas BERGER (www.sqlserver.fr)

-- Declaration des variables
declare @FK table (fk_id int, colonnes varchar(max) DEFAULT '')
declare @Index table (object_id int, index_id int, colonnes varchar(max) DEFAULT '')

declare @id1 int, @id2 int, @id3 int

-- Initialisation
insert into @FK (fk_id) select fk.object_id from sys.foreign_keys fk
insert into @Index (object_id,index_id) select i.object_id,i.index_id from sys.indexes i

-- Listes des colonnes des FK
DECLARE fk CURSOR FOR 
SELECT fkc.constraint_object_id,fkc.parent_column_id
FROM sys.foreign_key_columns fkc
order by fkc.constraint_column_id

OPEN fk
FETCH NEXT FROM fk INTO @id1,@id2

WHILE @@FETCH_STATUS = 0
BEGIN

	update @Fk
	set colonnes+=convert(varchar(max),@id2)+','
	where fk_id=@id1

	FETCH NEXT FROM fk INTO @id1,@id2
END

CLOSE fk
DEALLOCATE fk

-- Liste des colonnes des index
DECLARE ind CURSOR FOR 
SELECT ic.object_id,ic.index_id,ic.column_id
FROM sys.index_columns ic
where key_ordinal>0
order by ic.key_ordinal

OPEN ind
FETCH NEXT FROM ind INTO @id1,@id2,@id3

WHILE @@FETCH_STATUS = 0
BEGIN

	update @Index
	set colonnes+=convert(varchar(max),@id3)+','
	where object_id=@id1 and index_id=@id2

	FETCH NEXT FROM ind INTO @id1,@id2,@id3
END

CLOSE ind
DEALLOCATE ind

-- FK sans index
select distinct QUOTENAME(s.name)+'.'+QUOTENAME(t.name) as [Table parente], 
                QUOTENAME(s_r.name)+'.'+QUOTENAME(t_r.name) as [Table de référence], 
                QUOTENAME(fk.name) as [Clé étrangère] 
from sys.foreign_keys fk
join @FK fk_col on fk_col.fk_id=fk.object_id
join sys.tables t on t.object_id=fk.parent_object_id 
join sys.schemas s on s.schema_id=t.schema_id 
join sys.tables t_r on t_r.object_id=fk.referenced_object_id 
join sys.schemas s_r on s_r.schema_id=t_r.schema_id 
left join @Index ind on ind.object_id=t.object_id
					and ind.colonnes LIKE fk_col.colonnes+'%'
where ind.object_id is null

N’hésitez pas à le lancer sur vos bases de développement voire de production, et vous risquez fort de vous apercevoir que certaines bases de données sont beaucoup moins bien conçues qu’on ne le pense…