Scripting your database for fun and profit

Inspired by a post by Andy Leonard here, I remembered that I'd been meaning to post a Powershell script that I wrote a while back.  What better time than now for an inaugural post!

param (

[string] $server = $(Read-Host -prompt "Server"),

[string] $database = $(Read-Host -prompt "Database"),

[string] $directory = $(Read-Host -prompt "Output Directory")

)



$srv = new-object ("Microsoft.SqlServer.Management.Smo.Server") $server

$srv.Initialize() | out-null

if ( $srv.Databases.Contains($database) ) {

$db = $srv.Databases[$database]

}

else {

throw "Database '" + $database + "' not found on '" + $server + "'. Aborting."

}

$scr = new-object "Microsoft.SqlServer.Management.Smo.Scripter"

$scr.Server = $srv



$header_opts = $scr.Options

$header_opts.IncludeDatabaseContext = $false

$header_opts.ToFileOnly = $true

$body_opts = New-Object ("Microsoft.SqlServer.Management.Smo.ScriptingOptions") $header_opts



$header_opts.AppendToFile = $false

$header_opts.IncludeHeaders = $false

$header_opts.IncludeIfNotExists = $true

$header_opts.ScriptDrops = $true



$body_opts.AppendToFile = $true

$body_opts.AnsiPadding = $true

$body_opts.ExtendedProperties = $true

$body_opts.NoCollation = $true

$body_opts.NoCommandTerminator = $false

$body_opts.Permissions = $true

$body_opts.SchemaQualify = $true

$body_opts.ScriptBatchTerminator = $true

$body_opts.ScriptSchema = $true





new-item -path $directory -name "Schema Objects\Views" -type directory -force

foreach ($a in $db.Views | where {$_.IsSystemObject -eq $false}) {

$filename = $directory + '\Schema Objects\views\' + $a.schema + '.' + $a.name + '.view.sql'

$header_opts.FileName = $body_opts.FileName = $filename

$scr.Options = $header_opts

$scr.Script($a)

$scr.Options = $body_opts

$scr.Script($a)

}



new-item -path $directory -name "Schema Objects\Stored Procedures" -type directory -force

foreach ($a in $db.StoredProcedures | where {$_.IsSystemObject -eq $false}) {

$filename = $directory + '\Schema Objects\Stored Procedures\' + $a.schema + '.' + $a.name + '.proc.sql'

$header_opts.FileName = $body_opts.FileName = $filename

$scr.Options = $header_opts

$scr.Script($a)

$scr.Options = $body_opts

$scr.Script($a)

}



new-item -path $directory -name 'Schema Objects\Functions' -type directory -force

foreach ($a in $db.UserDefinedFunctions | where {$_.IsSystemObject -eq $false}) {

$filename = $directory + '\Schema Objects\Functions\' + $a.schema + '.' + $a.name + '.function.sql'

$header_opts.FileName = $body_opts.FileName = $filename

$scr.Options = $header_opts

$scr.Script($a)

$scr.Options = $body_opts

$scr.Script($a)

}



new-item -path $directory -name "Schema Objects\Triggers" -type directory -force

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

foreach ($a in $table.Triggers) {

$filename = $directory + '\Schema Objects\Triggers\' + `

$table.schema + '.' + $table.name + '.' + `

$a.name + '.trigger.sql'

$header_opts.FileName = $body_opts.FileName = $filename

$scr.Options = $header_opts

$scr.Script($a)

$scr.Options = $body_opts

$scr.Script($a)

}

}

---

Toss that into a file and run it through Powershell. A word of warning: it will clobber any directory that you point it at for the output. The interesting thing here is that you can get the behavior that Mr. Leonard wants: a guarded 'drop procedure' and a 'create procedure' that's not in an sp_executesql. Have fun...but remember don't run with scissors!