Script SQL Objects with Powershell

Like so many of you out there in Internetland, I’m fairly new to Powershell, which is fairly new to the world. Recently I wanted a simple way to script out database objects, and a couple of you were kind enough to share your solutions with me.

Sean Likes Powershell

I also worked with Sean, who likes very much to talk about Powershell (I recommend you start with What Makes Powershell Awesome?, my personal favorite blog on PS). Here is the current list of his Powershell videos:

Yes, I am bragging on him. You should listen to your friend Sean, he’s a cool dude.

I Like Powershell

Anyway, the Scripting DB Objects in PowerShell video was almost what I needed, but not quite. Sean goes over how to script out objects to a single file; I’m scripting objects to check into source control, so I want one object per file. Last night we finally got around to sitting down so he could show me the code.

dir | %{$Table = $_.Name; $_.script() | out-file c:\$Table.txt}

That’s it. That’s everything you need to script out every single table in a database to an individual file. I <3 Sean, and I <3 Powershell. At the risk of stealing his thunder, let me `splain a little.

We All Like Powershell!

There are SMO solutions, but there’s a definite efficiency, elegance, and convenience to having everything you need in a single line of code.

dir | %{$Table = $_.Name; $_.script() | out-file c:\$Table.txt}

Start SQLPS from within SSMS 2008 (just right-click your database of choice in the Object Explorer, and “Start Powershell”). The SQL Server Powershell will open, with the path to your chosen database already loaded. Type cd Tables and hit Enter to navigate to the Tables “folder”. Now, to break down the Powershell command:

  1. dir
    dir
    gets a listing of all the tables in that database
  2. dir |
    the pipe (|) pipes that list to the next part of the command
  3. dir | %{}
    %{}
    is the shorthand version of a FOR loop in PS*. So this FOR loop is FOR each table piped from dir
  4. dir | %{$Table = $_.Name}
    $Table
    is a variable. We set it = the current table name passed in to the loop, which is signified by $_.Name. Note that $_ always means “the current one”, in this case “the current table”.
  5. dir | %{$Table = $_.Name; $_.script()}
    Within the loop, after we set $Table = the current table name, we make the command to script out that current ($_) table with $_.script()
  6. dir | %{$Table = $_.Name; $_.script() | out-file c:\$Table.txt}
    We pipe the output of that scripting function (|) to an out-file command, with the path that we want to use. If we wanted to, we could use a single file andu use -append; but I want one file per table, so we use a filepath plus $Table.txt (here, c:\$Table.txt)

That’s all there is to it. It seems like gibberish at first, but that’s why they call it “code”, right?

Let me take a moment here to throw a little love in Allen White’s direction (blog, @sqlrunr on Twitter). Allen also likes to talk about Powershell and SQL, and is also a really cool dude, so he’s yet another resource to check out on the path to enlightenment.

Happy days,

Jen McCown

http://www.MidnightDBA.com

* Sean says, “Don’t forget your curlies!”

12 thoughts on “Script SQL Objects with Powershell”

  1. # Many thanks to Brian Brooks for this script that help me so much.
    #This one does not generate one per table but 1 per object type.
    #You should have your list of intances in instance_list.txt
    #The output is generated in .\sqlschemas folder

    # LoopInstances

    $InstanceListFile = “.\instance_list.txt”

    $InstanceList = Get-Content $InstanceListFile
    foreach ($InstanceName in $InstanceList)
    {
    # Configuration

    [System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SMO”) | Out-Null
    [System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.Replication”) | Out-Null
    [System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.RMO”) | Out-Null

    $MyScripter = New-Object “Microsoft.SqlServer.Management.SMO.Scripter”

    # $mySrvConn = New-Object “Microsoft.SqlServer.Management.Common.ServerConnection”
    # $mySrvConn.ServerInstance=$InstanceName
    # $mySrvConn.LoginSecure = $false
    # $mySrvConn.Login = “sa”
    # $mySrvConn.Password = “”

    $InstanceObject = New-Object “Microsoft.SqlServer.Management.SMO.Server” “$InstanceName”
    $RepInstanceObject = New-Object “Microsoft.SqlServer.Replication.ReplicationServer” “$InstanceName”
    # $InstanceObject = New-Object “Microsoft.SqlServer.Management.SMO.Server” ($mySrvConn)
    # $RepInstanceObject = New-Object “Microsoft.SqlServer.Replication.ReplicationServer” ($mySrvConn)

    $InstanceObject.SetDefaultInitFields(“Microsoft.SqlServer.Management.SMO.ExtendedStoredProcedure”, “IsSystemObject”)
    $InstanceObject.SetDefaultInitFields(“Microsoft.SqlServer.Management.SMO.StoredProcedure”, “IsSystemObject”)
    $InstanceObject.SetDefaultInitFields(“Microsoft.SqlServer.Management.SMO.Table”, “IsSystemObject”)
    $InstanceObject.SetDefaultInitFields(“Microsoft.SqlServer.Management.SMO.View”, “IsSystemObject”)
    $InstanceObject.SetDefaultInitFields(“Microsoft.SqlServer.Management.SMO.UserDefinedFunction”, “IsSystemObject”)
    $MyScripter.Server = $InstanceObject
    $ScripterOptions = $MyScripter.Options

    # SetScriptOptions

    $ScripterOptions.ToFileOnly = $True
    $ScripterOptions.AppendToFile = $True
    $ScripterOptions.ContinueScriptingOnError = $True
    $ScripterOptions.LoginSid = $True
    $ScripterOptions.AgentAlertJob = $True
    $ScripterOptions.AgentNotify = $True
    $ScripterOptions.Bindings = $True
    $ScripterOptions.DriAll = $True
    $ScripterOptions.Indexes = $True
    $ScripterOptions.Permissions = $True
    $ScripterOptions.SchemaQualify = $True
    $ScripterOptions.Triggers = $True
    $ScripterOptions.IncludeIfNotExists = $True

    # SetSchemaPath

    $CurrentDay = Get-Date -Format yyyyMMdd
    $SchemaPath = “.\SQLSchemas\” + $CurrentDay + “\” + $InstanceName
    if ((Test-Path $SchemaPath) -ne $True) {mkdir $SchemaPath | Out-Null}

    # DisplayInstance

    Write-Host “————————————————————”
    Write-Host $InstanceName – (Get-Date)
    Write-Host “————————————————————”

    # CheckVersion

    if ($InstanceObject.Information.VersionString.substring(0,2) -eq 10)
    {

    $InstanceObjectList = “Audits”,”BackupDevices”,”Credentials”,”CryptographicProviders”,”Databases”,”EndPoints”,”LinkedServers”,”Logins”,”ServerAuditSpecifications”,”Triggers”,”UserDefinedMessages”
    $JobServerObjectList = “AlertCategories”,”Alerts”,”JobCategories”,”Jobs”,”OperatorCategories”,”Operators”,”ProxyAccounts”,”SharedSchedules”,”TargetServerGroups”,”TargetServers”
    $DatabaseObjectList = “Assemblies”,”AsymmetricKeys”,”Certificates”,”DatabaseAuditSpecifications”,”Defaults”,”ExtendedProperties”,”FullTextCatalogs”,”FullTextStopLists”,”PartitionFunctions”,”PartitionSchemes”,”PlanGuides”,”Roles”,”Rules”,”Schemas”,”Synonyms”,”Triggers”,”UserDefinedAggregates”,”UserDefinedDataTypes”,”UserDefinedTableTypes”,”UserDefinedTypes”,”Users”,”XmlSchemaCollections”
    }

    if ($InstanceObject.Information.VersionString.substring(0,1) -eq 9)
    {
    $InstanceObjectList = “BackupDevices”,”Credentials”,”Databases”,”EndPoints”,”LinkedServers”,”Logins”,”Triggers”,”UserDefinedMessages”
    $JobServerObjectList = “AlertCategories”,”Alerts”,”JobCategories”,”Jobs”,”OperatorCategories”,”Operators”,”ProxyAccounts”,”SharedSchedules”,”TargetServerGroups”,”TargetServers”
    $DatabaseObjectList = “Assemblies”,”AsymmetricKeys”,”Certificates”,”Defaults”,”ExtendedProperties”,”FullTextCatalogs”,”PartitionFunctions”,”PartitionSchemes”,”Roles”,”Rules”,”Schemas”,”Synonyms”,”Triggers”,”UserDefinedAggregates”,”UserDefinedDataTypes”,”UserDefinedTypes”,”Users”,”XmlSchemaCollections”
    }

    if ($InstanceObject.Information.VersionString.substring(0,1) -eq 8)
    {
    $InstanceObjectList = “BackupDevices”,”Databases”,”LinkedServers”,”Logins”,”UserDefinedMessages”
    $JobServerObjectList = “AlertCategories”,”Alerts”,”JobCategories”,”Jobs”,”OperatorCategories”,”Operators”,”TargetServerGroups”,”TargetServers”
    $DatabaseObjectList = “Defaults”,”ExtendedProperties”,”FullTextCatalogs”,”Roles”,”Rules”,”UserDefinedDataTypes”,”Users”
    }

    # ScriptInstance

    Write-Host Instance
    foreach($Object in $InstanceObjectList)
    {
    Write-Host ” ” $Object
    $ScripterOptions.FileName=$SchemaPath + “\Instance_” + $Object + “.sql”
    if ((Test-Path $ScripterOptions.FileName) -eq $True)
    {
    del $ScripterOptions.FileName
    }
    $MyScripter.Script($InstanceObject.$Object)
    if ($? -eq $False)
    {
    Write-Host “FAILED – SCRIPT ABORTED”
    exit 1
    }
    }

    # ScriptJobServer

    if ($InstanceObject.Information.Edition -ne ‘Express Edition’)
    {
    Write-Host JobServer
    foreach($Object in $JobServerObjectList)
    {
    Write-Host ” ” $Object
    $ScripterOptions.FileName=$SchemaPath + “\JobServer_” + $Object + “.sql”
    if ((Test-Path $ScripterOptions.FileName) -eq $True)
    {
    del $ScripterOptions.FileName
    }
    $MyScripter.Script($InstanceObject.JobServer.$Object)
    if ($? -eq $False)
    {
    Write-Host “FAILED – SCRIPT ABORTED”
    exit 1
    }
    }
    }

    # ScriptDatabases

    $DatabaseFilter = $InstanceObject.Databases | where {$_.Name -ne “distribution”} | where {$_.Name -ne “master”} | where {$_.Name -ne “msdb”} | where {$_.Name -ne “ReportServer”} | where {$_.Name -ne “ReportServerTempDB”} | where {$_.Name -ne “tempdb”}
    foreach($Database in $DatabaseFilter)
    {
    Write-Host $Database
    foreach($Object in $DatabaseObjectList)
    {
    Write-Host ” ” $Object
    $ScripterOptions.FileName=$SchemaPath + “\Database_” + $Database.name + “_” + $Object + “.sql”
    if ((Test-Path $ScripterOptions.FileName) -eq $True)
    {
    del $ScripterOptions.FileName
    }
    $ScripterOptions.Permissions = $True
    $MyScripter.Script($InstanceObject.Databases[$Database.name].$Object)
    if ($? -eq $False)
    {
    Write-Host “FAILED – SCRIPT ABORTED”
    exit 1
    }
    }

    foreach($Object in “ExtendedStoredProcedures”,”StoredProcedures”,”Tables”,”Views”,”UserDefinedFunctions”)
    {
    Write-Host ” ” $Object
    $ScripterOptions.FileName=$SchemaPath + “\Database_” + $Database.name + “_” + $Object + “.sql”
    if ((Test-Path $ScripterOptions.FileName) -eq $True)
    {
    del $ScripterOptions.FileName
    }
    $ObjectFilter = $InstanceObject.Databases[$Database.name].$Object | where {$_.IsSystemObject -eq $False}
    if ($ObjectFilter)
    {
    $ScripterOptions.Permissions = $True
    $MyScripter.Script($ObjectFilter)
    if ($? -eq $False)
    {
    Write-Host “FAILED – SCRIPT ABORTED”
    exit 1
    }
    }
    }
    }

    # ScriptReplication – Added after all SMO scripting to avoid bug detected in losing the database context

    $RepScriptOptions = [Microsoft.SqlServer.Replication.scriptoptions]::Creation `
    -bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludeArticles `
    -bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludePublisherSideSubscriptions `
    -bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludeSubscriberSideSubscriptions

    Write-Host Replication
    foreach($ReplicatedDatabase in $RepInstanceObject.ReplicationDatabases)
    {
    $ScriptFileName=$SchemaPath + “\Replication_” + $ReplicatedDatabase.Name + “.sql”
    if ((Test-Path $ScriptFileName) -eq $True)
    {
    del $ScriptFileName
    }
    foreach($Publication in $ReplicatedDatabase.TransPublications)
    {
    Write-Host ” ” $Publication.Name
    $Publication.Script($RepScriptOptions) >> $ScriptFileName
    if ($? -eq $False)
    {
    Write-Host “FAILED – SCRIPT ABORTED”
    exit 1
    }
    }
    foreach($Publication in $ReplicatedDatabase.MergePublications)
    {
    Write-Host ” ” $Publication.Name
    $Publication.Script($RepScriptOptions) >> $ScriptFileName
    if ($? -eq $False)
    {
    Write-Host “FAILED – SCRIPT ABORTED”
    exit 1
    }
    }
    }
    }

  2. I’ll say it again, Sri: There are SMO solutions (like you posted), but there’s definitely efficiency, elegance, and convenience to having everything you need in a SINGLE line of code:

    dir | %{$Table = $_.Name; $_.script() | out-file c:\$Table.txt}

    All you need to do is run this same script in another folder (Views, UserDefinedFunctions, etc) to get those objects. I like this way better :)

  3. Amen to elegance but when u want to script all instances in your environment , including server level objects and each and every db object , I struggle to find elegant solutions. Let me know if you find one.

  4. Sri’s solution is OK. It’s a lot better than the general run of scripting routines one sees. It is doing something a lot different to your one-liner, and obviously a lot more useful. The trouble is that there are a lot of different demands for scripting. Do you want, for example, dependent objects? (you generally do with tables, for example, but occasionally not) Do you want the extended properties (fancy stripping all your comments out of your database accidentally?)
    It also runs in ordinary powershell 2 whereas yours only runs in SQLPS which is a cobbled powershell 1. This is fine for one-liners, but many scripts tend to be complex schedule-based things for doing nightly saves into Source-control or continuous integration, that use the PS2 features.
    Try replicating the functionality in Siri’s code and you’ll have a lot of ‘one-liners!

    @Siri. Try using the EnumObjects function. It is a bit tricky to use, but it will make your code a LOT shorter!

    @Jen. It wasn’t a freudian slip, just an ordinary one.

    1. Phil,

      You absolutely have some valid points, but remember the start of the post: I wanted a simple way to script out database objects. The one-line script does suffer from the weaknesses you point out, but it met my meager requirements at the time, and I’ll stand by that.

      And, it could well have been a Freudian slip…if you felt about Powershell like some do.. O.o
      -J

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>