{"id":602,"date":"2012-07-19T22:29:40","date_gmt":"2012-07-19T20:29:40","guid":{"rendered":"http:\/\/www.sqlserver.fr\/blog\/?p=602"},"modified":"2020-09-01T10:06:08","modified_gmt":"2020-09-01T08:06:08","slug":"generer-un-script-de-reconstruction-de-cles-primaire","status":"publish","type":"post","link":"https:\/\/www.sqlserver.fr\/blog\/generer-un-script-de-reconstruction-de-cles-primaire\/","title":{"rendered":"G\u00e9n\u00e9rer un script de reconstruction de cl\u00e9s primaires"},"content":{"rendered":"<p>Dans le m\u00eame contexte que la reconstruction d&rsquo;indexes mentionn\u00e9e <a title=\"G\u00e9n\u00e9rer un script de reconstruction d\u2019index\" href=\"https:\/\/www.sqlserver.fr\/blog\/generer-script-de-reconstruction-dindex\/\">ici<\/a>, voici le script compl\u00e9mentaire li\u00e9 \u00e0 la reconstruction de cl\u00e9s primaires. Etant donn\u00e9 qu&rsquo;il n&rsquo;existe pas de syntaxe permettant que cr\u00e9er une contrainte de cl\u00e9 \u00e9trang\u00e8re en mode \u00ab\u00a0remplacement\u00a0\u00bb, je pr\u00e9senterai ci-dessous un script g\u00e9n\u00e9rant la suppression des contraintes de cl\u00e9 primaire, et un script permettant de les r\u00e9g\u00e9n\u00e9rer.<\/p>\n<p><!--more--><\/p>\n<p>Voici donc un premier script assez simple pour supprimer toutes les contraintes de cl\u00e9 primaire.<\/p>\n<pre class=\"brush: sql; gutter: false; first-line: 1\">-- G\u00e9n\u00e9rateur de script de suppressions de cl\u00e9s primaires\r\n-- Auteur : Jean-Nicolas BERGER (www.sqlserver.fr)\r\n\r\nselect 'ALTER TABLE '+quotename(s.name)+'.'+quotename(t.name)+' DROP CONSTRAINT '+ quotename(i.name) +';'\r\nfrom sys.tables t\r\njoin sys.schemas s on s.schema_id=t.schema_id\r\njoin sys.indexes i on i.object_id=t.object_id\r\nwhere i.is_primary_key=1<\/pre>\n<p>Mais avant de supprimer les contraintes, il ne faut pas oublier de pr\u00e9parer un script pour les r\u00e9g\u00e9n\u00e9rer&#8230;<\/p>\n<pre class=\"brush: sql; gutter: false; first-line: 1\">-- G\u00e9n\u00e9rateur de script de cr\u00e9ation de cl\u00e9s primaires\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\tListeColonnes varchar(max))\r\n\r\ndeclare @index_id int,@object_id int\r\ndeclare @ListeColonnes 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'ALTER TABLE ' +QUOTENAME(s.name)+'.'+QUOTENAME(t.name)+' ADD CONSTRAINT '\r\n\t\t+ QUOTENAME(i.name) + ' PRIMARY KEY '+\r\n\t\t+ case when i.index_id=1 then 'CLUSTERED ' else 'NONCLUSTERED ' end\r\n\t\t+ ' (',\r\n\t\t+') WITH ('\r\n\t\t+ 'PAD_INDEX=' + case when i.is_padded=1 then 'ON' else 'OFF' end\r\n\t\t+ ',FILLFACTOR = ' + convert(varchar,case when i.fill_factor=0 then 100 else i.fill_factor end)\r\n\t\t+ ',SORT_IN_TEMPDB = ON'\r\n\t\t+ ',IGNORE_DUP_KEY = ' + case when i.ignore_dup_key=1 then 'ON' else 'OFF' end\r\n\t\t+ ',ALLOW_ROW_LOCKS = ' + case when i.allow_row_locks=1 then 'ON' else 'OFF' end\r\n\t\t+ ',ALLOW_PAGE_LOCKS = ' + case when i.allow_page_locks=1 then 'ON' else 'OFF' end\r\n\t\t+') ON '+QUOTENAME(ds.name)\r\n\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.is_primary_key=1 and t.type='U'\r\n\r\n-- Pour chaque PK\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 de la cl\u00e9 primaire\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-- Mise en forme de la liste de colonnes\r\n\tSELECT @ListeColonnes=SUBSTRING(@ListeColonnes,2,len(@ListeColonnes)-2)\r\n\r\n\tupdate @Liste\r\n\tset ListeColonnes=@ListeColonnes\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 + FinCreate +';' from @Liste<\/pre>\n<p>N&rsquo;h\u00e9sitez pas \u00e0 me faire part de vos remarques&#8230;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Dans le m\u00eame contexte que la reconstruction d&rsquo;indexes mentionn\u00e9e ici, voici le script compl\u00e9mentaire li\u00e9 \u00e0 la reconstruction de cl\u00e9s primaires. Etant donn\u00e9 qu&rsquo;il n&rsquo;existe pas de syntaxe permettant que cr\u00e9er une contrainte de cl\u00e9 \u00e9trang\u00e8re en mode \u00ab\u00a0remplacement\u00a0\u00bb, je &hellip; <a href=\"https:\/\/www.sqlserver.fr\/blog\/generer-un-script-de-reconstruction-de-cles-primaire\/\">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-602","post","type-post","status-publish","format-standard","hentry","category-outils"],"_links":{"self":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts\/602","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=602"}],"version-history":[{"count":7,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts\/602\/revisions"}],"predecessor-version":[{"id":1674,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts\/602\/revisions\/1674"}],"wp:attachment":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/media?parent=602"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/categories?post=602"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/tags?post=602"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}