Summary: Learn how to use Windows PowerShell to automate creation of SQL Database objects such as tables, stored procedures, and functions.

 

Hey, Scripting Guy! How can I use Windows PowerShell to script out SQL Server database objects?

— AA

 

Hello AA, Microsoft Scripting Guy Ed Wilson here. Today we have one final blog post from Aaron Nelson before he heads off to SQL PASS summit 2010.

 

Aaron Nelson is a Senior SQL Server Architect with over 10 years of experience in architecture, business intelligence, development, and performance tuning of SQL Server. He has experience managing enterprise-wide data needs in both transactional and data warehouse environments. Aaron holds certifications for MCITP Business Intelligence Developer, Database Administrator, Database Developer; and MCTS: Windows Server Virtualization, Configuration (meaning Hyper-V). Aaron Nelson ( @SQLvariant ) SQLvariant.com/wordpress/

Aaron helps run the AppDev and PowerShell Virtual Chapters of PASS, volunteers for the local PASS Chapter AtlantaMDF, co-runs the Atlanta PowerShell User Group, and help organize SQL Saturday in Atlanta. Again, here’s Aaron.

 

Scripting SQL Server Databases with Windows PowerShell

Speaking of train stations, did you know that when you land at Atlanta Hartsfield-Jackson International Airport you can take a MARTA train straight to the heart of downtown Atlanta? The Georgia World Congress Center is just one stop away from Five Points station and that is where Tech·Ed is being held in 2011. On behalf of the Atlanta PowerShell User Group we sure hope that you can make it out here next year J   Dr Scripto can be seen in the following image checking it out.

 

A vital task in SQL Server (or just about any RDBMS for that matter) is being able to script out database objects (tables, stored procedures, functions, and more) so that you can check them into source control, create deployment packages, create a new database from tables in an existing database (think data warehouse), compare between Prod and Dev. These are just some reasons people around the world have for scripting out portions or even all of their databases but in the decade or more that I have been working with databases these are some of the most prevalent reasons.

Today I want to share with you some quirks and one outright pitfall that I ran into when trying to determine how to best script out databases with Windows PowerShell to achieve different goals. Before I go on though I want to make sure that you know that we, as SQL Server DBAs, have never had an easier time of scripting out databases than we do today with Windows PowerShell. It is just, well; I am one that is fine with learning the hard way as long as we are accomplishing tasks along the way. Therefore, I want to save you the trouble of a few lessons I learned the old-fashioned way and here’s number one.

 

Simple Scripting From the Provider

I was so excited when I first discovered how easy it is to script out objects by using Windows PowerShell that I got a little ahead of myself. You see it is easy to script out just about anything in SQL Server but like the saying goes “the devil is in the details”. Just look:

cd SQLSERVER:\sql\WIN7NetBook\Default\Databases\ADVENTUREWORKS\TABLES

$PTH = get-item Production.TransactionHistory

$PTH.Script()

That was so easy wasn’t it? I mean, as long as you remembered to load those snap-ins from the other day. That is pretty cool but on closer examination a SQL person will start to ask: Where’s the Primary Key? Where are the Indexes? What about scripting Foreign Keys? What if it already exists, how do I make it generate the Drop statement? It just so happens that I have a funny story about that. I was so excited that I could script out objects and just wanted to understand how to generate those DROP statements. So I grabbed my variable and piped it to Get-Member like you are supposed to do ( $PTH | Get-Member ) and when I saw that it spit out a method called “Drop” I wanted to shout hooray! Except, being caught up in the moment, I did not stop to think what I was looking at and so guess what I did: $PTH.Drop() Turns out, that does not ‘Script the Drop’ it ‘Does the Drop’ so be careful with that one.

You can even script out all tables from right here with a fairly small amount of code:

foreach ($tbl in Get-ChildItem SQLSERVER:\SQL\Win7NetBook\Default\DATABASES\ADVENTUREWORKS\TABLES )

{
$k=”C:\Temp\” + $($tbl.Schema) + “.” + $($tbl.name) + “_table.SQL”
$tbl.Script() > $k
}

The only drawback to the approach that was discussed earlier is that we (Data Professionals) tend to want some more fine-grained control over scripting out all the objects in our databases. There are lots of options available to you when you scripting out a databases and if you are not used to working with databases every day that you may not be aware of just how many there are. To take a quick look at some of them you can just right-click a database in SSMS > select Tasks > and Generate Scripts. See the following figure.

 

From there you just have to select some objects to script out and then click the Advanced button when you find the scripting options the SMO has made available to you. As seen in the following figure.

 

 

To have all these options available to us we have to follow these steps.

1. Load theMicrosoft.SqlServer.SMO Assembly and then instantiate an instance of theSMO ServerObject.
2. Give it the name of a database that we want to script out.
3. Grab up all the tables, views, stored procedures, and functionsinto our$Objects variable.
4. Start to build a directory to work in based on the current date and time so that we can script out the database again later and it will not overwrite anything.
5. Grab the first object that we want to script out and start a loop.
6. Instantiate an instance of theScripterObject; and set a bunch of options for the objects that we want to script out.
7. Finish building our directory structure and file name.
8. Finally, we will script out one of the objects.

See the following figure for an idea of how this will work.

 

For the most part this handles many the things that we may be interested in but I found some completely unexpected behavior when I flipped the $scriptr.Options.ScriptDrops option from $False to $True. Instead of getting a nice little drop statement at the top of the same script that I had generated previously, all that I got was the Drop statement. What I had to do was instantiate another scripter object every time that I passed through the foreach loop, generate the drop statement separately, and then just append it to the file.

 

Scripting Out the Whole Database from a Function

I’ve created the following script to make my life easier when I have to script out a database and especially when I then have to check those scripts into source control.  It includes the drop statements that I needed:

function global:Script-DBObjectsIntoFolders([string]$server, [string]$dbname){

[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SMO”) | out-null

    $SMOserver = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) -argumentlist $server

    $db = $SMOserver.databases[$dbname]

 

$Objects = $db.Tables

$Objects += $db.Views

$Objects += $db.StoredProcedures

$Objects += $db.UserDefinedFunctions

 

#Build this portion of the directory structure out here in case scripting takes more than one minute.

$SavePath = “C:\TEMP\Databases\” + $($dbname)

$DateFolder = get-date -format yyyyMMddHHmm

new-item -type directory -name “$DateFolder”-path “$SavePath”

 

foreach ($ScriptThis in $Objects | where {!($_.IsSystemObject)}) {

#Need to Add Some mkDirs for the different $Fldr=$ScriptThis.GetType().Name

$scriptr = new-object (‘Microsoft.SqlServer.Management.Smo.Scripter’) ($SMOserver)

$scriptr.Options.AppendToFile = $True

$scriptr.Options.AllowSystemObjects = $False

$scriptr.Options.ClusteredIndexes = $True

$scriptr.Options.DriAll = $True

$scriptr.Options.ScriptDrops = $False

$scriptr.Options.IncludeHeaders = $True

$scriptr.Options.ToFileOnly = $True

$scriptr.Options.Indexes = $True

$scriptr.Options.Permissions = $True

$scriptr.Options.WithDependencies = $False

<#Script the Drop too#>

$ScriptDrop = new-object (‘Microsoft.SqlServer.Management.Smo.Scripter’) ($SMOserver)

$ScriptDrop.Options.AppendToFile = $True

$ScriptDrop.Options.AllowSystemObjects = $False

$ScriptDrop.Options.ClusteredIndexes = $True

$ScriptDrop.Options.DriAll = $True

$ScriptDrop.Options.ScriptDrops = $True

$ScriptDrop.Options.IncludeHeaders = $True

$ScriptDrop.Options.ToFileOnly = $True

$ScriptDrop.Options.Indexes = $True

$ScriptDrop.Options.WithDependencies = $False

 

<#This section builds folder structures.  Remove the date folder if you want to overwrite#>

$TypeFolder=$ScriptThis.GetType().Name

if ((Test-Path -Path “$SavePath\$DateFolder\$TypeFolder”) -eq “true”) ‘

        {“Scripting Out $TypeFolder $ScriptThis”} ‘

    else {new-item -type directory -name “$TypeFolder”-path “$SavePath\$DateFolder”}

$ScriptFile = $ScriptThis -replace “\[|\]”

$ScriptDrop.Options.FileName = “” + $($SavePath) + “\” + $($DateFolder) + “\” + $($TypeFolder) + “\” + $($ScriptFile) + “.SQL”

$scriptr.Options.FileName = “$SavePath\$DateFolder\$TypeFolder\$ScriptFile.SQL”

 

#This is where each object actually gets scripted one at a time.

$ScriptDrop.Script($ScriptThis)

$scriptr.Script($ScriptThis)

} #This ends the loop

} #This completes the function

 

In the script above I did a bit more than just add the drop to the loop. I also turned the whole script into a function. This enables me to call it like a stored procedure any time I want to script out a database on any of my Computers That Are Running SQL Server.  Like this:

Script-DBObjectsIntoFolders “WIN7AS400” “AdventureWorksLT”

 

I also included an example of variable expansion inside strings when I built the string for where it should store the file after it creates each object. If you examine $ScriptDrop.Options.FileName and $scriptr.Options.FileName you can see that they end up producing the same file name with two different approaches. The second approach is something that would be great to have in SQL. While I am building the string, Windows PowerShell can expand the variable and put whatever it is currently set to inside the string that I built.

 

Scripting Dependent Objects

One final feature that I want to make sure to point out is the WithDependencies option.  When I use this feature I am typically only going after a single object (table, stored proc). When you set the WithDependencies option to $True the Scripter makes sure to include other database objects on which the object that you are trying to script out depends. This can be useful when you are trying to deploy a new table to production or when you have to change the data type of a column. After we have loaded up this function into our environment we can just call it as follows:

Script-AnObject “TransactionHistory” “AdventureWorks” “WIN7NetBook”

function global:Script-AnObject([string]$dbname, [string]$server, [string]$objname){

[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SMO”) | out-null

$SMOserver = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) -argumentlist $server

$db = $SMOserver.databases[$dbname]

 

$Objects = $db.Tables

$Objects += $db.Views

$Objects += $db.StoredProcedures

$Objects += $db.UserDefinedFunctions

 

foreach ($ScriptThis in $Objects | where {!($_.IsSystemObject) -and $_.Name -eq “$objname”}) {

 

$scriptr = new-object (‘Microsoft.SqlServer.Management.Smo.Scripter’) ($SMOserver)

$scriptr.Options.AppendToFile = $False

$scriptr.Options.AllowSystemObjects = $False

$scriptr.Options.ClusteredIndexes = $True

$scriptr.Options.DriAll = $True

$scriptr.Options.ScriptDrops = $False

$scriptr.Options.IncludeHeaders = $True

$scriptr.Options.ToFileOnly = $True

$scriptr.Options.Indexes = $True

$scriptr.Options.WithDependencies = $True

 

$ScriptFile = $ScriptThis -replace “\[|\]”

$scriptr.Options.FileName = “C:\TEMP\Databases\” + $($dbname) + “\” + $($ScriptFile) + “.SQL”

 

$scriptr.Script($ScriptThis)

}

}

 

Now, Start Scripting!

I hope that these capabilities and approaches are helpful in your environment and make your learning curve with Windows PowerShell somewhat easier. Please feel free to reach out to me on twitter @SQLvariant if you have questions around a use case that I did not cover or look at my blog for more information.

 

AA, that is all there is to using Windows PowerShell to script SQL Server databases.  SQL guest blogger week will continue tomorrow when Sean McCown will talk about how to add users to DB.

I invite you to follow me on Twitter or Facebook. If you have any questions, send email to me at scripter@microsoft.com or post them on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.

 

Ed Wilson, Microsoft Scripting Guy