{"id":76,"date":"2014-08-20T00:00:00","date_gmt":"2013-01-01T00:00:00","guid":{"rendered":"https:\/\/www.sqlhammer.com\/blog\/scripting-objects-with-powershell"},"modified":"2026-03-18T21:51:08","modified_gmt":"2026-03-18T21:51:08","slug":"scripting-objects-with-powershell","status":"publish","type":"post","link":"https:\/\/sqlhammer.com\/index.php\/2014\/08\/20\/scripting-objects-with-powershell\/","title":{"rendered":"Scripting Objects with PowerShell"},"content":{"rendered":"

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\u2019s capabilities in this regard. Simply right-click on your object and navigate to the \u201cScript object<\/em> as\u201d sub-menu.<\/p>\n

\"\"<\/a><\/p>\n

The SSMS method doesn\u2019t work well if you want to automate the process for taking snapshots of schemas or if you just don\u2019t like that white background and prefer the PowerShell blue instead.<\/p>\n

Enter SQL Server Management Objects (SMO)<\/h2>\n

Using PowerShell we can access SMOs. To accomplish our task of progammatically scripting out objects we will need to get familiar with the\u00a0Microsoft.SqlServer.Management.Smo.Scripter<\/em> class. Class Definition<\/a>.<\/p>\n

The Scripter object constructor\u00a0accepts a Microsoft.SqlServer.Management.Smo.Server<\/em> object, so let\u2019s get that object ready.<\/p>\n

Import-Module sqlps -DisableNameChecking\n$server = New-Object Microsoft.SqlServer.Management.Smo.Server(\"localhost\\SQL2014\");\n$scripter = new-object Microsoft.SqlServer.Management.Smo.Scripter($server);\n$database = $server.Databases[\"SQLHammerRocks\"];<\/code><\/pre>\n
If you don\u2019t have the sqlps module available to you, you can load the necessary assemblies instead.<\/p>\n
add-type -AssemblyName \"Microsoft.SqlServer.ConnectionInfo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91\"\nadd-type -AssemblyName \"Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91\"\nadd-type -AssemblyName \"Microsoft.SqlServer.SMOExtended, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91\"\nadd-type -AssemblyName \"Microsoft.SqlServer.SqlEnum, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91\"\nadd-type -AssemblyName \"Microsoft.SqlServer.Management.Sdk.Sfc, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91\"\n\n
$server = New-Object Microsoft.SqlServer.Management.Smo.Server(\"localhost\\SQL2014\");\n$scripter = new-object Microsoft.SqlServer.Management.Smo.Scripter($server);\n$database = $server.Databases[\"SQLHammerRocks\"];<\/code><\/pre>\n<\/p>\n
Scripter options<\/h2>\n
Once the base objects are created we can configure the ScriptingOptions.\u00a0<\/em>There are a large number of options, as seen here<\/a>, but only a few\u00a0of the most common ones will be covered here.<\/p>\n
ScriptBatchTerminator
\nIncludes a batch terminator at the end of each object scripted.<\/p>\n
FileName
\nFull file path to generate script output into.<\/p>\n
ToFileOnly
\nIf true, only outputs to file rather than file and to screen.<\/p>\n
Permissions
\nIncludes a batch terminator at the end of each object scripted.<\/p>\n
DriAll
\nIncludes all Declarative Referential Integrity objects such as constraints.<\/p>\n
Triggers
\nIncludes triggers.<\/p>\n
Indexes
\nIncludes indexes.<\/p>\n
$scripter.options.ScriptBatchTerminator = $true\n$scripter.options.FileName = \"C:\\BestTableEverCreated.sql\"\n$scripter.options.ToFileOnly = $true\n$scripter.options.Permissions = $true\n$scripter.options.DriAll = $true\n$scripter.options.Triggers = $true\n$scripter.options.Indexes = $true<\/code><\/pre>\n
Script method inputs<\/h2>\n
There are two ways to use the Script()<\/em> method. The most straight forward is to pass in a Microsoft.SqlServer.Management.Smo.SqlSmoObject <\/em>and the next is to pass in a\u00a0Uniform Resource Name (URN). A URN\u00a0is 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.<\/p>\n
$table = $database.Tables[\"BestTableEverCreated\"];\n$scripter.Script([Microsoft.SqlServer.Management.Smo.SqlSmoObject[]]$table);<\/code><\/pre>\n
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\u00a0SmoObject method\u00a0turns into a lot of trial and error. Instead, you can use the URNs like this.<\/p>\n
$dbObj = $database.enumobjects() | where { $_.name -eq \"BestTableEverCreated\" }\n$urn = new-object Microsoft.SqlServer.Management.Sdk.Sfc.Urn($dbObj.Urn);\n$scripter.Script($urn);<\/code><\/pre>\n
Full script<\/h2>\n
SmoObject method<\/h3>\n
Import-Module sqlps -DisableNameChecking\n$server = New-Object Microsoft.SqlServer.Management.Smo.Server(\"KINGFERGUS\\SQL2014\");\n$database = $server.Databases[\"SQLHammerRocks\"];\n$scripter = new-object Microsoft.SqlServer.Management.Smo.Scripter($server);\n\n
$scripter.options.ScriptBatchTerminator = $true\n$scripter.options.FileName = \"C:\\BestTableEverCreated.sql\"\n$scripter.options.ToFileOnly = $true\n$scripter.options.Permissions = $true\n$scripter.options.DriAll = $true\n$scripter.options.Triggers = $true\n$scripter.options.Indexes = $true<\/p>\n\n
$table = $database.Tables[\"BestTableEverCreated\"];\n$scripter.Script([Microsoft.SqlServer.Management.Smo.SqlSmoObject[]]$table);<\/code><\/pre>\n<\/p>\n
Urn method<\/h3>\n
Import-Module sqlps -DisableNameChecking\n$server = New-Object Microsoft.SqlServer.Management.Smo.Server(\"KINGFERGUS\\SQL2014\");\n$database = $server.Databases[\"SQLHammerRocks\"];\n$scripter = new-object Microsoft.SqlServer.Management.Smo.Scripter($server);\n\n
$scripter.options.ScriptBatchTerminator = $true\n$scripter.options.FileName = \"C:\\BestTableEverCreated.sql\"\n$scripter.options.ToFileOnly = $true\n$scripter.options.Permissions = $true\n$scripter.options.DriAll = $true\n$scripter.options.Triggers = $true\n$scripter.options.Indexes = $true<\/p>\n\n
$dbObj = $database.enumobjects() | where { $_.name -eq \"BestTableEverCreated\" }\n$urn = new-object Microsoft.SqlServer.Management.Sdk.Sfc.Urn($dbObj.Urn);\n$scripter.Script($urn);<\/code><\/pre>\n<\/p>\n
\n
\"\"Written by Derik Hammer of SQL Hammer<\/a><\/strong><\/p>\n
Derik is a data professional focusing on Microsoft SQL Server. His passion focuses around high-availability<\/a>, disaster recovery<\/a>, continuous integration, and automated maintenance. his experience has spanned long-term database administration, consulting, and\u00a0entrepreneurial ventures.<\/p>\n
Derik gives the SQL community credit for plugging the gaps in his knowledge when he was a junior DBA and, now that his skills have matured, started SQLHammer.com<\/a> as one small way to give back and continue the cycle of shared learning.<\/p>\n
Derik is the owner and lead author of SQL Hammer, a Microsoft SQL Server resource<\/a>.<\/p>\n
For more information, visit http:\/\/www.sqlhammer.com<\/a>. Follow Derik on Twitter<\/a> for SQL tips and chat<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"
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\u2019s capabilities in this regard. Simply right-click on your object and navigate to the \u201cScript object as\u201d sub-menu. The SSMS method doesn\u2019t work well if you want to automate the process […]<\/p>\n","protected":false},"author":1,"featured_media":566,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-76","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/76","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/comments?post=76"}],"version-history":[{"count":1,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/76\/revisions"}],"predecessor-version":[{"id":339,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/76\/revisions\/339"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media\/566"}],"wp:attachment":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media?parent=76"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/categories?post=76"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/tags?post=76"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}