Dans la s\u00e9rie des petites scripts utiles, voici une instruction simple permettant d’identifier les contraintes de cl\u00e9s \u00e9trang\u00e8res pour lesquelles il manque un index dans la table parente. Un tel manque d’index peut parfois provoquer des temps de r\u00e9ponse extr\u00eamement important pour de simples suppressions unitaires d’enregistrements dans une table de r\u00e9f\u00e9rence.<\/p>\n
Voici donc un script permettant de lister l’ensemble des cl\u00e9s \u00e9trang\u00e8res pour lesquelles un index d’appui manque, ainsi que les tables concern\u00e9es par ces cl\u00e9s.<\/p>\n
-- Liste des cl\u00e9s \u00e9trang\u00e8res avec index absent\r\n-- Auteur : Jean-Nicolas BERGER (www.sqlserver.fr)\r\n\r\n-- Declaration des variables\r\ndeclare @FK table (fk_id int, colonnes varchar(max) DEFAULT '')\r\ndeclare @Index table (object_id int, index_id int, colonnes varchar(max) DEFAULT '')\r\n\r\ndeclare @id1 int, @id2 int, @id3 int\r\n\r\n-- Initialisation\r\ninsert into @FK (fk_id) select fk.object_id from sys.foreign_keys fk\r\ninsert into @Index (object_id,index_id) select i.object_id,i.index_id from sys.indexes i\r\n\r\n-- Listes des colonnes des FK\r\nDECLARE fk CURSOR FOR \r\nSELECT fkc.constraint_object_id,fkc.parent_column_id\r\nFROM sys.foreign_key_columns fkc\r\norder by fkc.constraint_column_id\r\n\r\nOPEN fk\r\nFETCH NEXT FROM fk INTO @id1,@id2\r\n\r\nWHILE @@FETCH_STATUS = 0\r\nBEGIN\r\n\r\n\tupdate @Fk\r\n\tset colonnes+=convert(varchar(max),@id2)+','\r\n\twhere fk_id=@id1\r\n\r\n\tFETCH NEXT FROM fk INTO @id1,@id2\r\nEND\r\n\r\nCLOSE fk\r\nDEALLOCATE fk\r\n\r\n-- Liste des colonnes des index\r\nDECLARE ind CURSOR FOR \r\nSELECT ic.object_id,ic.index_id,ic.column_id\r\nFROM sys.index_columns ic\r\nwhere key_ordinal>0\r\norder by ic.key_ordinal\r\n\r\nOPEN ind\r\nFETCH NEXT FROM ind INTO @id1,@id2,@id3\r\n\r\nWHILE @@FETCH_STATUS = 0\r\nBEGIN\r\n\r\n\tupdate @Index\r\n\tset colonnes+=convert(varchar(max),@id3)+','\r\n\twhere object_id=@id1 and index_id=@id2\r\n\r\n\tFETCH NEXT FROM ind INTO @id1,@id2,@id3\r\nEND\r\n\r\nCLOSE ind\r\nDEALLOCATE ind\r\n\r\n-- FK sans index\r\nselect distinct QUOTENAME(s.name)+'.'+QUOTENAME(t.name) as [Table parente], \r\n QUOTENAME(s_r.name)+'.'+QUOTENAME(t_r.name) as [Table de r\u00e9f\u00e9rence], \r\n QUOTENAME(fk.name) as [Cl\u00e9 \u00e9trang\u00e8re] \r\nfrom sys.foreign_keys fk\r\njoin @FK fk_col on fk_col.fk_id=fk.object_id\r\njoin sys.tables t on t.object_id=fk.parent_object_id \r\njoin sys.schemas s on s.schema_id=t.schema_id \r\njoin sys.tables t_r on t_r.object_id=fk.referenced_object_id \r\njoin sys.schemas s_r on s_r.schema_id=t_r.schema_id \r\nleft join @Index ind on ind.object_id=t.object_id\r\n\t\t\t\t\tand ind.colonnes LIKE fk_col.colonnes+'%'\r\nwhere ind.object_id is null<\/pre>\nN’h\u00e9sitez pas \u00e0 le lancer sur vos bases de d\u00e9veloppement voire de production, et vous risquez fort de vous apercevoir que certaines bases de donn\u00e9es sont beaucoup moins bien con\u00e7ues qu’on ne le pense…<\/p>\n","protected":false},"excerpt":{"rendered":"
Dans la s\u00e9rie des petites scripts utiles, voici une instruction simple permettant d’identifier les contraintes de cl\u00e9s \u00e9trang\u00e8res pour lesquelles il manque un index dans la table parente. Un tel manque d’index peut parfois provoquer des temps de r\u00e9ponse extr\u00eamement … Continuer la lecture