Using PowerShell and SMO to Script SQL Server Database Objects

I’ve been telling myself to learn PowerShell for quite awhile but I’ve never been able to get over the learning curve.  What I needed was a piece of work that was bigger than trivial and smaller than huge that would force me into PowerShell.  It came along this week in the form of a request to script all the views in a test database before the refresh from production and then “putting them back” into the new test database.  Doing this from SSMS is pretty easy but I sure don’t want to do it every time we refresh.  I needed something repeatable, simple, and parameter driven. 

 I knew PS would be relatively simple to manage so I looked into using PS with SQL Server Management Objects (SMO).  First I searched the Web for something like “PowerShell SMO Script Objects” and found this, which pointed me in the right direction. 

The complete script is at the bottom of the article but I’ve broken it up into sections which I’ll cover in detail.  My focus is those of us learning PS so if you are a guru please be patient with the rest of us.

 

Figure 1

All the user configurable parameters can be set in Figure 1 above.  In line 13 note that $ObjType will be inserted as a literal in the code so PowerShell will read MyObject.$ObjType but execute MyObject.views.  This allows you to script tables, views, stored procedures, and other Db objects by simply changing the parameters.  For even more flexibility you could read in parameters from a text file.

Also note in line 15 that the “@” indicates a list of string objects that will be excluded from the script.  You could also add other schemas if you wish.

Finally, the If block (lines 18-21) tests for the output file and deletes it if it exists.  Without this check successive runs of the script would append the same scripts to the file.

 

Figure 2

Next, let’s look at the code to create the SMO we’ll use to script our objects.  Line 25 tells PowerShell which assembly we want to use so it can create the objects we need.  Lines 28 and 29 create the Server and Databases collection objects respectively.  Note that the Server object takes the instance name on creation.

Once you have the DB collection available you are almost ready to start scripting.  The Script method is available for each object (table, view, etc.) but there are a number of options you can use to control the scripts generated.  For example, if you want to test for existence before creating an object you would set the IncludeIfNotExists = 1.  To use the option you will create a ScriptingOptions object (line 32) and set various properties as needed.  A complete list of options is here.

If you want to perform multiple scripting tasks, say Drop and then Create you can perform the scripting multiple times, appending the results as we see in the following two code blocks.

 

Figure 3

First you set the ScriptingOptions as in Figure 3 for the operation you want to perform (lines 38 & 50).  Then you iterate through every object of the specified type in the database using a foreach loop (lines 41 & 53).  Each time the foreach starts it filters out the object if the schema is any one of the values passed in from the list discussed above.

Without the whitespace, the whole PowerShell script takes up only about 20 lines.  As you can see, PowerShell and SMO together make a very powerful tool to put in your toolbox.    I’d be interested to hear about how you modified this code to meet your own needs.

Here is the whole script, ready to use with your parameters.

##########################################################
# PS Script DB Objects
#
# Uses PS and SMO to script the drop and create a type of
# SQL Server object (views, tables, etc.)
#
# Tom Powell                                       2/24/2011
##########################################################
 
#Setup####################################################
    $SQLInstance = “Server\Instance”
    $DB = “DBName ”
    $ObjType = “views”
    $OutFile = “C:\temp\Scripts.sql”
    $ExcludeSchemas = @(“sys”,”Information_Schema”) #List of schemas to exclude
   
#Create smo objects##########################################
    [System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null
   
    #SQL Instance & DB object
    $s = new-object (‘Microsoft.SqlServer.Management.Smo.Server’) $SQLInstance
    $dbs=$s.Databases
   
    #ScriptingOption object to pass parms for Script method
    $so = new-object (‘Microsoft.SqlServer.Management.Smo.ScriptingOptions’)
    $so.IncludeIfNotExists = 1
    $so.SchemaQualify = 1
    $so.AllowSystemObjects = 0

#Script Drop Objects###########################################
    $so.ScriptDrops = 1         #Script Drop Objects

    #Generate script for all objects##################################
    foreach ($objs in $dbs[$DB].$ObjType)
    {
        If ($ExcludeSchemas -notcontains $objs.Schema )  
        {
            $objs.Script($so) + “`r GO `r ” | out-File $OutFile #-Append
        }
    }

#Script Create Objects#########################################
    $so.ScriptDrops = 0         #Script Create Objects

    #Generate script for all objects#################################
    foreach ($objs in $dbs[$DB].$ObjType)
    {
        If ($ExcludeSchemas -notcontains $objs.Schema )
        {
            $objs.Script($so) + “`r GO `r ” | out-File $OutFile -Append
        }
    }

Advertisements

6 thoughts on “Using PowerShell and SMO to Script SQL Server Database Objects

    • This is the only script I have found on the internet that actually works. It’s my first adventure with PowerShell and you made it easy.
      Thanks.

  1. Line 15: $ExcludeSchemas = @(“sys”,”Information_Schema”) #List of schemas to exclude

    Have you found a way to exclude all the views that exist in the “System Views” folder?

  2. Thanks, Tom, this is exactly what I needed. Looking around, there are a few articles on how to do this, but yours is the simplest and clearest. Just one thing I got from another article – be sure to exclude system objects or you get a lot of (probably) unwanted objects!

    foreach ($objs in $dbs[$DB].$ObjType | where {!($_.IsSystemObject)})

    Thanks!

    Steve

  3. This is a good article. I recently wrote a script that will do basically the same thing, except for every database on a server. The problem I’m running into is how to script database objects on a SQL Server on the network, but not on the local machine from where the PowerShell script is being ran. Any thoughts?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s