I assume that by now you’ve tried the script from Part 1, and have seen how easy it is to script out your database objects using Powershell.  The resulting scripts are the standard DDL scripts that you can generate from Management Studio, and for most of us, are just fine right out of the box.

What if, however, we need to customize those scripts?  In my environment, our software deployments (including the database scripts) are automated, and we need certain elements to be included in the scripts for the automation to function properly.  I also like to include a certain degree of fail-safe in the scripts, to prevent accidentally deploying an object to the wrong database, or possibly wiping out a table full of data.

Turns out that it’s a snap to customize the scripts that are generated, using simple text piping, just like you’ve been doing in batch files for a decade or two.  Simply insert the following into the script from Part 1.  For your convenience, the entire script is repeated at the end of this post.

I’ll resume this series after SQL Pass.  I’m not attending, but I will be taking some time off next week, and with all of the buzz around the conference, nobody will be paying attention to little ol’ me anyway!

    #Add commented USE statement for deployment validation tool
    "USE $dbname" >  "$SavePath\$TypeFolder\$ScriptFile.SQL"
    #Add existing object handler (drop procedure, table exists, etc..)
    if ($TypeFolder -eq "StoredProcedure") {
    "IF OBJECT_ID('$ScriptFile', 'P') IS NOT NULL
        BEGIN
        DROP PROCEDURE $ScriptFile
        END
    GO
    " >> "$SavePath\$TypeFolder\$ScriptFile.SQL"
    } elseif ($TypeFolder -eq "View") {
    "IF OBJECT_ID('$ScriptFile', 'V') IS NOT NULL
        BEGIN
        DROP VIEW $ScriptFile
        END
    GO
    " >> "$SavePath\$TypeFolder\$ScriptFile.SQL"
    } elseif ($TypeFolder -eq "UserDefinedFunction") {
    "IF OBJECT_ID('$ScriptFile', 'FN') IS NOT NULL OR OBJECT_ID('$ScriptFile', 'TF') IS NOT NULL
        BEGIN
        DROP FUNCTION $ScriptFile
        END
    GO
    " >> "$SavePath\$TypeFolder\$ScriptFile.SQL"
    } elseif ($TypeFolder -eq "Trigger") {
    "IF OBJECT_ID('$ScriptFile', 'TR') IS NOT NULL
        BEGIN
        DROP TRIGGER $ScriptFile
        END
    GO
    " >> "$SavePath\$TypeFolder\$ScriptFile.SQL"
    } elseif ($TypeFolder -eq "Table") {
    "IF OBJECT_ID('$ScriptFile', 'U') IS NOT NULL
        BEGIN
        IF EXISTS(SELECT * FROM $ScriptFile)
            BEGIN
            USE tempdb
            RAISERROR('Table exists and contains data, cannot proceed.', 16, 1)
            RETURN
            END
        ELSE
            BEGIN
            DROP TABLE $ScriptFile
            END
        END
    GO
    " >> "$SavePath\$TypeFolder\$ScriptFile.SQL"
    }

The complete script now looks like this:

#Set server and database names here
$server = "REALServer"
$dbname = "REALDB"
#Create SMO connection to DB
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
    $SMOserver = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $server
    $db = $SMOserver.databases[$dbname]
#Build collection of objects to script
$Objects = $db.Tables
$Objects += $db.Views
$Objects += $db.StoredProcedures
$Objects += $db.UserDefinedFunctions
#Set destination for generated scripts
$SavePath = "C:\temp\db\Source\" + $($dbname)
#Loop through the object collection
foreach ($ScriptThis in $Objects | where {!($_.IsSystemObject)}) {
#Create Scripter object
    $scriptr = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($SMOserver)
#Set options for generated scripts
    $scriptr.Options.AppendToFile = $True
    $scriptr.Options.AllowSystemObjects = $False
    $scriptr.Options.ClusteredIndexes = $True
    $scriptr.Options.DriAll = $True
    $scriptr.Options.ScriptDrops = $False
    $scriptr.Options.IncludeHeaders = $False
    $scriptr.Options.ToFileOnly = $True
    $scriptr.Options.Indexes = $True
    $scriptr.Options.Permissions = $True
    $scriptr.Options.WithDependencies = $False
#Create destination folder and subfolder, if necessary
    $TypeFolder=$ScriptThis.GetType().Name
    if ((Test-Path -Path "$SavePath\$TypeFolder") -eq "true")
            {"Scripting Out $TypeFolder $ScriptThis"}
        else {new-item -type directory -name "$TypeFolder"-path "$SavePath"}
    $ScriptFile = $ScriptThis -replace "\[|\]"
#Set output filename for script
    $scriptr.Options.FileName = "$SavePath\$TypeFolder\$ScriptFile.SQL"
    #Add commented USE statement for deployment validation tool
    "USE $dbname" >  "$SavePath\$TypeFolder\$ScriptFile.SQL"
    #Add existing object handler (drop procedure, table exists, etc..)
    if ($TypeFolder -eq "StoredProcedure") {
    "IF OBJECT_ID('$ScriptFile', 'P') IS NOT NULL
        BEGIN
        DROP PROCEDURE $ScriptFile
        END
    GO
    " >> "$SavePath\$TypeFolder\$ScriptFile.SQL"
    } elseif ($TypeFolder -eq "View") {
    "IF OBJECT_ID('$ScriptFile', 'V') IS NOT NULL
        BEGIN
        DROP VIEW $ScriptFile
        END
    GO
    " >> "$SavePath\$TypeFolder\$ScriptFile.SQL"
    } elseif ($TypeFolder -eq "UserDefinedFunction") {
    "IF OBJECT_ID('$ScriptFile', 'FN') IS NOT NULL OR OBJECT_ID('$ScriptFile', 'TF') IS NOT NULL
        BEGIN
        DROP FUNCTION $ScriptFile
        END
    GO
    " >> "$SavePath\$TypeFolder\$ScriptFile.SQL"
    } elseif ($TypeFolder -eq "Trigger") {
    "IF OBJECT_ID('$ScriptFile', 'TR') IS NOT NULL
        BEGIN
        DROP TRIGGER $ScriptFile
        END
    GO
    " >> "$SavePath\$TypeFolder\$ScriptFile.SQL"
    } elseif ($TypeFolder -eq "Table") {
    "IF OBJECT_ID('$ScriptFile', 'U') IS NOT NULL
        BEGIN
        IF EXISTS(SELECT * FROM $ScriptFile)
            BEGIN
            USE tempdb
            RAISERROR('Table exists and contains data, cannot proceed.', 16, 1)
            RETURN
            END
        ELSE
            BEGIN
            DROP TABLE $ScriptFile
            END
        END
    GO
    " >> "$SavePath\$TypeFolder\$ScriptFile.SQL"
    }
#Generate script for this object
    $scriptr.Script($ScriptThis)
}
#Second loop through tables to script triggers
foreach ($Table in $db.Tables | where {!($_.IsSystemObject)}) {
#Loop through triggers on this table
    foreach ($ScriptThis in $Table.Triggers) {
#Create Scripter object
        $scriptr = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($SMOserver)
#Set options for generated scripts
        $scriptr.Options.AppendToFile = $True
        $scriptr.Options.AllowSystemObjects = $False
        $scriptr.Options.ClusteredIndexes = $True
        $scriptr.Options.DriAll = $True
        $scriptr.Options.ScriptDrops = $False
        $scriptr.Options.IncludeHeaders = $False
        $scriptr.Options.ToFileOnly = $True
        $scriptr.Options.Indexes = $True
        $scriptr.Options.Permissions = $True
        $scriptr.Options.WithDependencies = $False
#Create destination folder and subfolder, if necessary
$TypeFolder=$ScriptThis.GetType().Name
        if ((Test-Path -Path "$SavePath\Trigger") -eq "true")
                {"Scripting Out Trigger $ScriptThis"}
            else {new-item -type directory -name "Trigger"-path "$SavePath"}
        $ScriptFile = $ScriptThis -replace "\[|\]"
#Set output filename for script
        $scriptr.Options.FileName = "$SavePath\Trigger\$ScriptFile.SQL"
#Add commented USE statement for deployment validation tool
"USE $dbname" >  "$SavePath\$TypeFolder\$ScriptFile.SQL"
        #Add existing object handler (drop trigger, etc..)
        "IF OBJECT_ID('$ScriptFile', 'TR') IS NOT NULL
            BEGIN
            DROP TRIGGER $ScriptFile
            END
        GO
        " >> "$SavePath\Trigger\$ScriptFile.SQL"
#Generate script for this trigger
        $scriptr.Script($ScriptThis)
}
}