Powershell: Script out SQL Objects

I'm still committed to realising my goal this year of using Powershell more, and this week I've come across a bonafide use for it. The use case I'm dealing with is the ever troublesome SQL source control issue and the connected nature of developing SQL objects (as opposed to scripts).

My idea was to just have a scheduled job to script out all the relevant SQL scripts from my shared development database server to a repository and then have these checked in to my source control provider. I'm not quite there yet, but I thought i'd share my script for scripting out objects and one of the cool short hands I found in PS.

Its stored as a ps1 script and you pass in the server you wish to interogate:

param($sqlserver)

$filePath = "C:\SQLScripts\Tables_" + $sqlserver + "_"

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-null

$srv = new-object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver
$options = new-object "Microsoft.SqlServer.Management.Smo.ScriptingOptions"
$options.AppendToFile = $true
$options.ToFileOnly = $true
$options.ClusteredIndexes = $true
$options.NonClusteredIndexes = $true
$options.DriAll = $true

foreach($db in $srv.Databases | where {$_.IsSystemObject -eq $false})
{
   $options.FileName = $filePath + $db.Name + ".sql"

   $tables = $db.Tables

   foreach($table in $tables | where {$_.IsSystemObject -eq $false})
   {
       $table.Script($options)

   }
}

The cool short hand i found was the ability to include a WHERE clause in the foreach loop essentially removing the need for a further if block within each loop:

foreach($table in $tables | where {$_.IsSystemObject -eq $false})

This is my first cut at doing this and I'll be looking to extend this to parameterise the scripts location and perhaps change to individual files. Still, another step on the powershell journey!