Scripting Objects with PowerShell

The ability to script out the definitions of objects is valuable to any DBA. Most of us are intimately familiar with SQL Server Management Studio’s capabilities in this regard. Simply right-click on your object and navigate to the “Script object as” sub-menu.

The SSMS method doesn’t work well if you want to automate the process for taking snapshots of schemas or if you just don’t like that white background and prefer the PowerShell blue instead.

Enter SQL Server Management Objects (SMO)

Using PowerShell we can access SMOs. To accomplish our task of progammatically scripting out objects we will need to get familiar with the Microsoft.SqlServer.Management.Smo.Scripter class. Class Definition.

The Scripter object constructor accepts a Microsoft.SqlServer.Management.Smo.Server object, so let’s get that object ready.

[sql]Import-Module sqlps -DisableNameChecking
$server = New-Object Microsoft.SqlServer.Management.Smo.Server(“localhost\SQL2014”);
$scripter = new-object Microsoft.SqlServer.Management.Smo.Scripter($server);
$database = $server.Databases[“SQLHammerRocks”];[/sql]

If you don’t have the sqlps module available to you, you can load the necessary assemblies instead.

[sql]add-type -AssemblyName “Microsoft.SqlServer.ConnectionInfo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91”
add-type -AssemblyName “Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91”
add-type -AssemblyName “Microsoft.SqlServer.SMOExtended, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91”
add-type -AssemblyName “Microsoft.SqlServer.SqlEnum, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91”
add-type -AssemblyName “Microsoft.SqlServer.Management.Sdk.Sfc, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91”

$server = New-Object Microsoft.SqlServer.Management.Smo.Server(“localhost\SQL2014”);
$scripter = new-object Microsoft.SqlServer.Management.Smo.Scripter($server);
$database = $server.Databases[“SQLHammerRocks”];[/sql]

Scripter options

Once the base objects are created we can configure the ScriptingOptions. There are a large number of options, as seen here, but only a few of the most common ones will be covered here.

[sql]$scripter.options.ScriptBatchTerminator = $true
$scripter.options.FileName = “C:\BestTableEverCreated.sql”
$scripter.options.ToFileOnly = $true
$scripter.options.Permissions = $true
$scripter.options.DriAll = $true
$scripter.options.Triggers = $true
$scripter.options.Indexes = $true[/sql]

Script method inputs

There are two ways to use the Script() method. The most straight forward is to pass in a Microsoft.SqlServer.Management.Smo.SqlSmoObject and the next is to pass in a Uniform Resource Name (URN). A URN is basically a primary key or unique identifier for the object that you would like to script out. This is a useful piece of information because it is not object type specific. For example, when scripting out a table, using the SmoObject method, you would need to create the object like this.

[sql]$table = $database.Tables[“BestTableEverCreated”];
$scripter.Script([Microsoft.SqlServer.Management.Smo.SqlSmoObject[]]$table);[/sql]

This works well if you know that your object is a table but if you are looking for an object by name but are not certain of the type the SmoObject method turns into a lot of trial and error. Instead, you can use the URNs like this.

[sql]$dbObj = $database.enumobjects() | where { $_.name -eq “BestTableEverCreated” }
$urn = new-object Microsoft.SqlServer.Management.Sdk.Sfc.Urn($dbObj.Urn);
$scripter.Script($urn);[/sql]

Full script

 SmoObject method

[sql]Import-Module sqlps -DisableNameChecking
$server = New-Object Microsoft.SqlServer.Management.Smo.Server(“KINGFERGUS\SQL2014”);
$database = $server.Databases[“SQLHammerRocks”];
$scripter = new-object Microsoft.SqlServer.Management.Smo.Scripter($server);

$scripter.options.ScriptBatchTerminator = $true
$scripter.options.FileName = “C:\BestTableEverCreated.sql”
$scripter.options.ToFileOnly = $true
$scripter.options.Permissions = $true
$scripter.options.DriAll = $true
$scripter.options.Triggers = $true
$scripter.options.Indexes = $true

$table = $database.Tables[“BestTableEverCreated”];
$scripter.Script([Microsoft.SqlServer.Management.Smo.SqlSmoObject[]]$table);[/sql]

Urn method

[sql]Import-Module sqlps -DisableNameChecking
$server = New-Object Microsoft.SqlServer.Management.Smo.Server(“KINGFERGUS\SQL2014”);
$database = $server.Databases[“SQLHammerRocks”];
$scripter = new-object Microsoft.SqlServer.Management.Smo.Scripter($server);

$scripter.options.ScriptBatchTerminator = $true
$scripter.options.FileName = “C:\BestTableEverCreated.sql”
$scripter.options.ToFileOnly = $true
$scripter.options.Permissions = $true
$scripter.options.DriAll = $true
$scripter.options.Triggers = $true
$scripter.options.Indexes = $true

$dbObj = $database.enumobjects() | where { $_.name -eq “BestTableEverCreated” }
$urn = new-object Microsoft.SqlServer.Management.Sdk.Sfc.Urn($dbObj.Urn);
$scripter.Script($urn);

[/sql]