Scripter la création et suppression des contraintes (ex : FK)

19 avril 2017

Commentaires fermés

SQL Mangament Studio ne permet pas de générer un script pour la création et suppression des contraintes seules. Néanmoins, on peut avoir recours à d’autres solutions telles que l’assembly SMO.

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)
$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)

param(
    [Parameter(Mandatory=$false)] [string]$Instance="localhost",
    [Parameter(Mandatory=$false)] [string]$Database="AdventureWorks2016CTP3",
    [Parameter(Mandatory=$false)] [string]$Username="sa",
    [Parameter(Mandatory=$false)] [string]$Password,
    [Parameter(Mandatory=$false)] [boolean]$ScriptToDrop=$False,
    [Parameter(Mandatory=$false)] [string]$ScriptPath 
    )

clear-host

$ScriptDirectory = 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= $ScriptDirectory }

    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.DriForeignKeys = $true;
    $scripter.Options.DriChecks = $true;
    $scripter.Options.ContinueScriptingOnError = $True
    $scripter.Options.IncludeIfNotExists = $True
    $scripter.Options.IncludeHeaders = $True
    $scripter.Options.ToFileOnly = $True
    $scripter.Options.IncludeDatabaseContext = $True
    $scripter.Options.FileName = "$ScriptPath\$($db.Name)_Constraints_" + $Action + ".sql"

    $smoObjects = New-Object Microsoft.SqlServer.Management.Smo.UrnCollection

    $dbObjCollection = @();

    foreach($tb in $db.Tables)
    {
      $dbObjCollection += $tb.Checks;
      $dbObjCollection += $tb.ForeignKeys;
    }

    foreach ($dbObj in $dbObjCollection) 
    {   
        If ($dbObj.Parent.IsSystemObject -eq $false) {       
            $smoObjects.Add($dbObj.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
}

param(

[Parameter(Mandatory=$false)] [string]$Instance="localhost",

[Parameter(Mandatory=$false)] [string]$Database="AdventureWorks2016CTP3",

[Parameter(Mandatory=$false)] [string]$Username="sa",

[Parameter(Mandatory=$false)] [string]$Password,

[Parameter(Mandatory=$false)] [boolean]$ScriptToDrop=$False,

[Parameter(Mandatory=$false)] [string]$ScriptPath

)

clear-host

$ScriptDirectory = 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= $ScriptDirectory }

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.DriForeignKeys = $true;

$scripter.Options.DriChecks = $true;

$scripter.Options.ContinueScriptingOnError = $True

$scripter.Options.IncludeIfNotExists = $True

$scripter.Options.IncludeHeaders = $True

$scripter.Options.ToFileOnly = $True

$scripter.Options.IncludeDatabaseContext = $True

$scripter.Options.FileName = "$ScriptPath\$($db.Name)_Constraints_" + $Action + ".sql"

$smoObjects = New-Object Microsoft.SqlServer.Management.Smo.UrnCollection

$dbObjCollection = @();

foreach($tb in $db.Tables)

{

$dbObjCollection += $tb.Checks;

$dbObjCollection += $tb.ForeignKeys;

}

foreach ($dbObj in $dbObjCollection)

{

If ($dbObj.Parent.IsSystemObject -eq $false) {

$smoObjects.Add($dbObj.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

}

Pour générer un script de création et de suppression des index :

https://www.concatskills.com/2017/04/03/scripter-creation-et-suppression-des-index/

Auteur

Sarah Béquet

Archietcte Data Microsoft, les maîtres mots sont : performance, industrialisation, méthodologie & bonne humeur.

Derniers articles

2019.12.02BI & Big Data Etude des contextes en DAX (4/4) : Transition de contexte
2019.11.25BI & Big Data Etude des contextes en DAX (3/4) : Contexte de requête
2019.11.17BI & Big Data Etude des contextes en DAX (2/4) : Contexte de filtre
2019.11.11BI & Big Data Etude des contextes en DAX (1/4) : Contexte de ligne

Articles liés

Droits Active Directory pour la création d'un groupe de disponibilité

Resource Governor

Parallélisme SSIS par package

Quelques trucs à savoir sur Invoke-Sqlcmd

Blog