Blog
Scripter la création et suppression des index
SQL Mangament Studio ne permet pas de générer un script pour la création et suppression des index seuls. Il existe plusieurs alternatives pour outrepasser cette limitation :
SMO via Powershell (ou C#)
SQL Server Management Objects (SMO) est une librairie permettant de scripter les objets Microsoft SQL Server. Dans le script Powershell ci-dessous, il y a quelques paramètres à renseigner :
$Instance : Instance concernée
$Database : Base de données concernée
$Username : Login pour authentification SQL (optionnel)
$Password : Password pour authentification SQL (optionnel)
$IncludeClusteredIndexes : Inclure les index cluster ou pas
$ScriptToDrop : Script de suppression ($True) ou de création ($False)
$ScriptPath : Chemin du script généré (par défaut : dossier où est stocké le script Powershell)
NB : Ce script gère aussi la création/suppression des index sur les vues indexées.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 |
param( [Parameter(Mandatory=$false)] [string]$Instance="localhost", [Parameter(Mandatory=$false)] [string]$Database="AdventureWorks2016CTP3", [Parameter(Mandatory=$false)] [string]$Username, [Parameter(Mandatory=$false)] [string]$Password, [Parameter(Mandatory=$false)] [boolean]$IncludeClusteredIndexes=$True, [Parameter(Mandatory=$false)] [boolean]$ScriptToDrop=$False, [Parameter(Mandatory=$false)] [string]$ScriptPath ) clear-host $scripterDirectory = Split-Path $MyInvocation.MyCommand.Path Try { [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | out-null [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null if (![string]::IsNullOrEmpty($Username) -And [string]::IsNullOrEmpty($Password)) { [System.Security.SecureString]$SecurePassword = Read-Host "Enter Password" -AsSecureString [String]$Password = [Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR($SecurePassword)); } if ([string]::IsNullOrEmpty($ScriptPath)) { $ScriptPath= $scripterDirectory } if ($ScriptToDrop -eq $False) { $Action = "Create" } else { $Action = "Drop" } $Conn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection $Conn.ServerInstance=$Instance $Server = New-Object Microsoft.SqlServer.Management.Smo.Server($Conn) if (![string]::IsNullOrEmpty($Username)) { $Server.ConnectionContext.LoginSecure = $false $Server.ConnectionContext.Login=$Username $Server.ConnectionContext.Password=$Password } $db = $Server.Databases.Item($Database) if ($db.name -ne $Database) { Throw "Can't find the database '$Database' in $Instance" } $scripter = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($Server) $scripter.Options.ScriptDrops = $scriptToDrop $scripter.Options.ClusteredIndexes = $IncludeClusteredIndexes $scripter.Options.DriAll = $True $scripter.Options.ContinueScriptingOnError = $True $scripter.Options.IncludeIfNotExists = $True $scripter.Options.IncludeHeaders = $True $scripter.Options.ToFileOnly = $True $scripter.Options.Indexes = $True $scripter.Options.WithDependencies = $False $scripter.Options.IncludeDatabaseContext = $True $scripter.Options.FileName = "$ScriptPath\$($db.Name)_IDX_" + $Action + ".sql" $smoObjects = New-Object Microsoft.SqlServer.Management.Smo.UrnCollection # Generate IDX script for tables foreach ($tb in $db.Tables) { If ($tb.IsSystemObject -eq $false) { foreach ($ix in $tb.Indexes) { $smoObjects.Add($ix.Urn) } } } # Generate IDX script for indexed view Foreach ($vw in $db.Views) { If ($vw.IsSystemObject -eq $false) { foreach ($ix in $vw.Indexes) { $smoObjects.Add($ix.Urn) } } } $sc = $scripter.Script($smoObjects) } Catch { $errorMessage = $_.Exception.Message $line = $_.InvocationInfo.ScriptLineNumber $script_name = $_.InvocationInfo.ScriptName Write-Host "Error: Occurred on line $line in script $script_name." -ForegroundColor Red Write-Host "Error: $ErrorMessage" -ForegroundColor Red } |
T-SQL
Plus simplement, on peut générer les scripts de création et de suppression d’index en interrogeant les tables système.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 |
USE [AdventureWorks2014] GO SELECT DropScript = 'IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N''' + QUOTENAME(Schema_name(T.Schema_id))+'.'+QUOTENAME(T.name) + ''') AND name = N''' + I.name + ''')' + CHAR(10) + 'DROP INDEX [' + I.name + '] ON ' + QUOTENAME(Schema_name(T.Schema_id))+'.'+QUOTENAME(T.name) + CHAR(10) + 'GO', CreateScript = 'CREATE ' + CASE WHEN I.is_unique = 1 THEN ' UNIQUE ' ELSE '' END + I.type_desc COLLATE DATABASE_DEFAULT +' INDEX ' + I.name + ' ON ' + Schema_name(T.Schema_id)+'.'+T.name + ' ( ' + KeyColumns + ' ) ' + ISNULL(' INCLUDE ('+IncludedColumns+' ) ','') + ISNULL(' WHERE '+I.Filter_definition,'') + ' WITH ( ' + CASE WHEN I.is_padded = 1 THEN ' PAD_INDEX = ON ' ELSE ' PAD_INDEX = OFF ' END + ',' + 'FILLFACTOR = '+CONVERT(CHAR(5),CASE WHEN I.Fill_factor = 0 THEN 100 ELSE I.Fill_factor END) + ',' + -- default value 'SORT_IN_TEMPDB = OFF ' + ',' + CASE WHEN I.ignore_dup_key = 1 THEN ' IGNORE_DUP_KEY = ON ' ELSE ' IGNORE_DUP_KEY = OFF ' END + ',' + CASE WHEN ST.no_recompute = 0 THEN ' STATISTICS_NORECOMPUTE = OFF ' ELSE ' STATISTICS_NORECOMPUTE = ON ' END + ',' + ' ONLINE = OFF ' + ',' + CASE WHEN I.allow_row_locks = 1 THEN ' ALLOW_ROW_LOCKS = ON ' ELSE ' ALLOW_ROW_LOCKS = OFF ' END + ',' + CASE WHEN I.allow_page_locks = 1 THEN ' ALLOW_PAGE_LOCKS = ON ' ELSE ' ALLOW_PAGE_LOCKS = OFF ' END + ' ) ON [' + DS.name + ' ]' + CHAR(10) + 'GO' FROM sys.indexes I JOIN sys.tables T ON T.Object_id = I.Object_id JOIN sys.sysindexes SI ON I.Object_id = SI.id AND I.index_id = SI.indid JOIN (SELECT * FROM ( SELECT IC2.object_id , IC2.index_id , STUFF((SELECT ' , ' + C.name + CASE WHEN MAX(CONVERT(INT,IC1.is_descending_key)) = 1 THEN ' DESC ' ELSE ' ASC ' END FROM sys.index_columns IC1 JOIN Sys.columns C ON C.object_id = IC1.object_id AND C.column_id = IC1.column_id AND IC1.is_included_column = 0 WHERE IC1.object_id = IC2.object_id AND IC1.index_id = IC2.index_id GROUP BY IC1.object_id,C.name,index_id ORDER BY MAX(IC1.key_ordinal) FOR XML PATH('')), 1, 2, '') KeyColumns FROM sys.index_columns IC2 GROUP BY IC2.object_id ,IC2.index_id) tmp3 )tmp4 ON I.object_id = tmp4.object_id AND I.Index_id = tmp4.index_id JOIN sys.stats ST ON ST.object_id = I.object_id AND ST.stats_id = I.index_id JOIN sys.data_spaces DS ON I.data_space_id=DS.data_space_id JOIN sys.filegroups FG ON I.data_space_id=FG.data_space_id LEFT JOIN (SELECT * FROM ( SELECT IC2.object_id , IC2.index_id , STUFF((SELECT ' , ' + C.name FROM sys.index_columns IC1 JOIN Sys.columns C ON C.object_id = IC1.object_id AND C.column_id = IC1.column_id AND IC1.is_included_column = 1 WHERE IC1.object_id = IC2.object_id AND IC1.index_id = IC2.index_id GROUP BY IC1.object_id,C.name,index_id FOR XML PATH('')), 1, 2, '') IncludedColumns FROM sys.index_columns IC2 GROUP BY IC2.object_id ,IC2.index_id) tmp1 WHERE IncludedColumns IS NOT NULL ) tmp2 ON tmp2.object_id = I.object_id AND tmp2.index_id = I.index_id WHERE I.is_primary_key = 0 AND I.is_unique_constraint = 0 --AND I.type_desc != 'CLUSTERED' |
Pour générer un script de création et de suppression des contraintes (ex : FK) :
https://www.concatskills.com/2017/04/19/scripter-creation-suppression-contraintes-fk/
Auteur
- Archietcte Data Microsoft, les maîtres mots sont : performance, industrialisation, méthodologie & bonne humeur.
Derniers articles
- 2019.12.02BI & Big DataEtude des contextes en DAX (4/4) : Transition de contexte
- 2019.11.25BI & Big DataEtude des contextes en DAX (3/4) : Contexte de requête
- 2019.11.17BI & Big DataEtude des contextes en DAX (2/4) : Contexte de filtre
- 2019.11.11BI & Big DataEtude des contextes en DAX (1/4) : Contexte de ligne