Home » Powershell »Random helpfulness »sql server »sqlserverpedia-syndication »Uncategorized » Currently Reading:

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!”

Currently there are "12 comments" on this Article:

  1. “Recently I wanted a simple way to script out all Powershell objects”

    Freudien slip there Jen? :)

  2. Jen says:

    Man, it took me 5 rereads and 2 days to figure out what was wrong with that sentence. Thanks Jamie!

  3. ha, sorry jen! Hope I didn’t waste too much of your time! :)

  4. Jen McCown says:

    Nono, not at all! I was just so CONFUSED…”What, what’s wrong with that? Freudian??” I kept looking for something inappropriate…

  5. Fantastic article! Plus I think you slipped in a pseudo-quote from Zoolander, so extra points for that!
    I can’t wait to try this out!

  6. Sri says:

    # 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
    }
    }
    }
    }

  7. Jen McCown says:

    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 :)

  8. Sri says:

    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.

  9. [...] Jen McCown (aka SQL Awesomesauce) posts many PowerShell video links Posted on July 22, 2010 by sqlmashup Script SQL Objects with Powershell [...]

  10. Phil Factor says:

    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.

    • Jen McCown says:

      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

Comment on this Article:







Minion Reindex by MidnightDBA is here!

 

Excellent Index Maintenance

Download Minion Reindex, log feature requests, read documentation, and sign up for the newsletter at MidnightSQL.com/Minion!


 

MidnightSQL Consulting

Need help? Got an emergency? Write us at Support@MidnightDBA.com!

We can schedule time to help with your backup/restore issues, high availability and disaster recovery setup, performance problems, and a great deal more. Very often, we're even available on the moment for downtime issues and emergencies.

For more information about MidnightSQL consulting, email us or check out www.MidnightSQL.com. Happy days!

Where are We?

November 3-7: PASS Summit, Seattle, WA

PASS Summit: Jen is presenting How to Interview a DBA: A Panel Debate on Thursday 11/6 1:30pm, room 401 (along with Adam Machanic, Sean McCown, Bob Pusateri, and Michelle Ufford).

PASS Summit: Sean is presenting Performance Tuning Your Backups on Wednesday 11/5 3:00pm, room 602-604.

December 11: Presenting "Powershell Cmdlets.." at Alaska SQL Server User Group

January 30: "Become an Enterprise DBA" precon at Austin SQL Saturday

Blog Posts by Category

DBAs@Midnight

How to Eat Pop-tarts
Watch DBAs@Midnight live on Fridays,m 11pm Central time

The best database career advice you’ve never heard!

DBARoadmap.com

The DBA Roadmap Seminar is 7 MP3 tracks (over 5 hours!) of insider guidance on your database career. We'll teach you how and what to study as a DBA, weigh in on controversial resume debates, teach you to recognize a worthy recruiter, and discuss the new professionalism of interviews. Also some bonus materials, PDF companion guides, and really spiffy intro music!

Once your $99 PayPal payment is submitted, you'll get the download link in e-mail! (Download is a 370Mb ZIP file.)

Become a DBA. Become a BETTER DBA. Use the Roadmap.

Visit www.DBARoadmap.com for info, forums, and more!

Add to Cart View Cart

Cunningham’s Law

"The best way to get the right answer on the Internet is not to ask a question, it's to post the wrong answer."
Relevant: http://xkcd.com/386/