Jen McCown

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:

• PowerShell Basics
• Scripting DB Objects in PowerShell
• Scripting DB Objects in Powershell (Advanced Topics)
• Working with PSDrive in PowerShell
• Powershell with –NoExit
• Change SQL Server Job Owner
• Grant DB Permissions
• Grant Schema Permissions in Powershell
• Building a Powershell BCP Routine
• Powershell Profiles
• Beginning Powershell for DBAs 1
• Beginning Powershell for DBAs 2
• Beginning Powershell for DBAs 3
• Beginning Powershell for DBAs 4
• Replace Text in File
• Find DB Files with Powershell
• Pass Params to Scripts (Powershell)
• Index Scripting Problems
• Change DB Compatibility Level in Powershell
• Get a Specific Method in Powershell
• Add User to Windows Group with Powershell
• Build .Net Objects in Powershell
• Start Agent Jobs in Powershell
• Start Agent Jobs in Powershell: Part 2
• Manage DB Files in Powershell

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