{"id":599,"date":"2012-07-18T00:00:58","date_gmt":"2012-07-17T22:00:58","guid":{"rendered":"http:\/\/www.sqlserver.fr\/blog\/?p=599"},"modified":"2026-05-02T14:31:08","modified_gmt":"2026-05-02T12:31:08","slug":"generer-script-de-reconstruction-dindex","status":"publish","type":"post","link":"https:\/\/www.sqlserver.fr\/blog\/generer-script-de-reconstruction-dindex\/","title":{"rendered":"G\u00e9n\u00e9rer un script de reconstruction d&rsquo;index"},"content":{"rendered":"<p>Certaines op\u00e9rations sur les index ne peuvent pas \u00eatre r\u00e9alis\u00e9es via la commande ALTER. Ainsi, par exemple, la modification de donn\u00e9es telles que le taux de remplissage (Fillfactor) ou encore les param\u00e9trages d&rsquo;autorisation de verrous ligne \/ page, n\u00e9cessite le passage par la commande de cr\u00e9ation CREATE INDEX.<\/p>\n<p>Le probl\u00e8me est qu&rsquo;il est n\u00e9cessaire de g\u00e9n\u00e9rer un script de reconstruction des index, en y apportant \u00e9ventuellement quelques modifications pour notre besoin propre (modifier le taux de remplissage, &#8230;).<!--more--><\/p>\n<p>Pour cela, je vous propose le script suivant, qui g\u00e9n\u00e9rera un ensemble de lignes de commandes que vous pourrez ais\u00e9ment int\u00e9grer dans votre processus d&rsquo;upgrade de base.<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">-- G\u00e9n\u00e9rateur de script de cr\u00e9ation d'index\r\n-- Auteur : Jean-Nicolas BERGER (www.sqlserver.fr)\r\n\r\nset nocount on\r\n\r\n-- D\u00e9claration de variables\r\ndeclare @Liste table (index_id int,\r\n\t\t\t\t\t\tobject_id int,\r\n\t\t\t\t\t\tDebutCreate varchar(max),\r\n\t\t\t\t\t\tFinCreate varchar(max),\r\n\t\t\t\t\t\tMilieuInclude varchar(max) DEFAULT ') INCLUDE (',\r\n\t\t\t\t\t\tListeColonnes varchar(max),\r\n\t\t\t\t\t\tListeColonnesIncluses varchar(max))\r\n\r\ndeclare @index_id int,@object_id int\r\ndeclare @ListeColonnes varchar(max), @ListeColonnesIncluses varchar(max)\r\n\r\n-- Index \u00e0 prendre en compte, avec mise en place des param\u00e8tres\r\ninsert into @Liste(index_id,object_id,DebutCreate,FinCreate)\r\nselect i.index_id,t.object_id,\r\n\t\t'CREATE '\r\n\t\t+ case when i.is_unique=1 then 'UNIQUE ' else '' end\r\n\t\t+ case when i.index_id=1 then 'CLUSTERED ' else 'NONCLUSTERED ' end\r\n\t\t+ 'INDEX '+QUOTENAME(i.name)+' ON '+QUOTENAME(s.name)+'.'+QUOTENAME(t.name)+' (',\r\n\t\tcase when i.filter_definition is null then '' else ') WHERE ('+ i.filter_definition end\r\n\t\t\t+') WITH ('\r\n\t\t\t+ 'PAD_INDEX=' + case when i.is_padded=1 then 'ON' else 'OFF' end\r\n\t\t\t+ ',FILLFACTOR = ' + convert(varchar,case when i.fill_factor=0 then 100 else i.fill_factor end)\r\n\t\t\t+ ',SORT_IN_TEMPDB = ON'\r\n\t\t\t+ ',IGNORE_DUP_KEY = ' + case when i.ignore_dup_key=1 then 'ON' else 'OFF' end\r\n\t\t\t+ ',DROP_EXISTING = ON'\r\n\t\t\t+ ',ALLOW_ROW_LOCKS = ' + case when i.allow_row_locks=1 then 'ON' else 'OFF' end\r\n\t\t\t+ ',ALLOW_PAGE_LOCKS = ' + case when i.allow_page_locks=1 then 'ON' else 'OFF' end\r\n\t\t\t+') ON '+QUOTENAME(ds.name)\r\n\t\t\t+case when ds.type='PS' then '('+QUOTENAME(cp.name)+')' else '' end\r\n\t\t from sys.indexes i\r\njoin sys.tables t on t.object_id=i.object_id\r\njoin sys.schemas s on s.schema_id=t.schema_id\r\njoin sys.data_spaces ds on ds.data_space_id=i.data_space_id\r\nleft join sys.index_columns icp on icp.object_id=i.object_id\r\n\t\t\t\t\t\t\t\tand icp.index_id=i.index_id\r\n\t\t\t\t\t\t\t\tand icp.partition_ordinal=1\r\nleft join sys.columns cp on cp.object_id=t.object_id\r\n\t\t\t\t\t\tand cp.column_id=icp.column_id\r\nwhere i.type in (1,2) and t.type='U' and i.is_primary_key=0\r\n\r\n-- Pour chaque index\r\nDECLARE curseur CURSOR FOR \r\nSELECT index_id,object_id\r\nFROM @Liste\r\n\r\nOPEN curseur\r\nFETCH NEXT FROM curseur INTO @index_id,@object_id\r\n\r\nWHILE @@FETCH_STATUS = 0\r\nBEGIN\r\n\t-- Construction de la liste des colonnes principales\r\n\tSELECT @ListeColonnes=','\r\n\tSELECT @ListeColonnes+=quotename(c.name)+case when ic.is_descending_key=1 then ' DESC' else ' ASC' end+','\r\n\tFROM sys.index_columns ic\r\n\t\tjoin sys.columns c on c.object_id=ic.object_id\r\n\t\t\t\t\t\t\tand c.column_id=ic.column_id\r\n\twhere ic.object_id=@object_id\r\n\t\tand ic.index_id=@index_id\r\n\t\tand ic.key_ordinal&gt;0\r\n\torder by key_ordinal\r\n\r\n\t-- Construction de la liste des colonnes incluses\r\n\tSELECT @ListeColonnesIncluses=','\r\n\tSELECT @ListeColonnesIncluses+=quotename(c.name)+','\r\n\tFROM sys.index_columns ic\r\n\t\tjoin sys.columns c on c.object_id=ic.object_id\r\n\t\t\t\t\t\t\tand c.column_id=ic.column_id\r\n\twhere ic.object_id=@object_id\r\n\t\tand ic.index_id=@index_id\r\n\t\tand ic.key_ordinal=0\r\n\r\n\t-- Mise en forme des listes de colonnes\r\n\tSELECT @ListeColonnes=SUBSTRING(@ListeColonnes,2,len(@ListeColonnes)-2)\r\n\tselect @ListeColonnesIncluses=nullif(@ListeColonnesIncluses,',')\r\n\tSELECT @ListeColonnesIncluses=SUBSTRING(@ListeColonnesIncluses,2,len(@ListeColonnesIncluses)-2)\r\n\r\n\tupdate @Liste\r\n\tset ListeColonnes=@ListeColonnes,\r\n\t\tListeColonnesIncluses=@ListeColonnesIncluses\r\n\twhere object_id=@object_id\r\n\t\tand index_id=@index_id\r\n\r\n\tFETCH NEXT FROM curseur INTO @index_id,@object_id\r\nEND\r\n\r\nCLOSE curseur\r\nDEALLOCATE curseur\r\n\r\n-- Pr\u00e9sentation du r\u00e9sultat final\r\nselect DebutCreate + ListeColonnes + isnull(MilieuInclude+ListeColonnesIncluses, '') + FinCreate +';' from @Liste<\/pre>\n<p>Comme vous pouvez le constater, ce script pr\u00e9sente les principaux param\u00e8tres de configuration d&rsquo;index (colonnes normales avec ordre ascendant ou descendant, colonnes incluses, filtres, partitionnement, &#8230;).<\/p>\n<p>Il se concentre uniquement sur les index hors cl\u00e9 primaire, un script d\u00e9di\u00e9 \u00e9tant pr\u00e9sent\u00e9 pour cela <a title=\"G\u00e9n\u00e9rer un script de reconstruction de cl\u00e9s primaires\" href=\"https:\/\/www.sqlserver.fr\/blog\/generer-un-script-de-reconstruction-de-cles-primaire\/\">dans un autre article<\/a>.<\/p>\n<p>N&rsquo;h\u00e9sitez pas \u00e0 le tester et \u00e0 m&rsquo;en donner des nouvelles.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Certaines op\u00e9rations sur les index ne peuvent pas \u00eatre r\u00e9alis\u00e9es via la commande ALTER. Ainsi, par exemple, la modification de donn\u00e9es telles que le taux de remplissage (Fillfactor) ou encore les param\u00e9trages d&rsquo;autorisation de verrous ligne \/ page, n\u00e9cessite le &hellip; <a href=\"https:\/\/www.sqlserver.fr\/blog\/generer-script-de-reconstruction-dindex\/\">Continuer la lecture <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[],"class_list":["post-599","post","type-post","status-publish","format-standard","hentry","category-outils"],"_links":{"self":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts\/599","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/comments?post=599"}],"version-history":[{"count":8,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts\/599\/revisions"}],"predecessor-version":[{"id":1948,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts\/599\/revisions\/1948"}],"wp:attachment":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/media?parent=599"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/categories?post=599"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/tags?post=599"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}