Off to see the wizard(s), the wonderful wizard(s) of PASS


This week I start the long journey to the PASS Summit 2010. My plans changed slightly since my initial post. I am now leaving a day earlier (Saturday) and fly from England to Amsterdam and then on to Seattle. The first flight is at 6am, so I will not be going to bed after my brother’s wedding, I will get out of the suit and into the taxi to the airport at 3am. I should arrive in Seattle on Saturday at about Midday local time. That gives me Sunday as a jet-lag recovery day.

I am taking a pre and post con (again). It makes so much sense when you travel half-way round the world to spend as much time in a class room!

I hope to see some familiar faces and meet up with people that I only know online. If you don’t belong to either of these groups, introduce yourself – I don’t bite 😮

Advertisements

Scripting DB Objects using Powershell


I recently had to script out all objects for our database at work so we could put it into TFS.  I am sure there are much better ways of doing this, but I wanted to learn Powershell and heard about SMO via simple-talk.

This is what I came up with (sorry about code formatting I haven’t found out how to improve that on wordpress yet):

# define parameters
param

(

$server = "",

$instance = "default",

$database = "",

$schema = "dbo",

$basefolder = "C:\temp\Powershell\$database\SchemaObjects\",

$objectoption = $(read-host "All Objects = 0, Tables = 1, Table Triggers = 2, Views = 3, Functions = 4, Stored Procedures = 5")
)

# trap errors

$errors = "C:\temp\Powershell\errors.txt"

trap

{

"______________________" | out-file $errors -append;

"ERROR SCRIPTING TABLES" | out-file $errors -append;

get-date | out-file $errors -append;

"ERROR: " + $_ | out-file $errors -append;

"`$server = $server" | out-file $errors -append;

"`$instance = $instance" | out-file $errors -append;

"`$database = $database" | out-file $errors -append;

"`$tables = $tables" | out-file $errors -append;

"`$path = $path" | out-file $errors -append;

"`$scripts = $scripts" | out-file $errors -append;

#throw "ERROR: See $errors"

}

# load .NET assembly
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
function MakeDirectory #Checks to see if the supplied Directory is there and creates it if not. Inside a Process to allow a Pipe to use it.
{
param([string]$DirName)
Process
{
if (!(Test-Path -path $DirName))
{
New-Item $DirName -type directory | Out-Null
}
}
}
cls
MakeDirectory ($basefolder)
# Create Server Object using SMO
$srv = new-object Microsoft.SqlServer.Management.Smo.Server($server)

# Create Database Object
$db =$srv.databases[$database]

# SMO Scripter creation and options set

$scr = New-Object "Microsoft.SqlServer.Management.Smo.Scripter"
$scr.Server = $srv
$options = New-Object "Microsoft.SqlServer.Management.SMO.ScriptingOptions"
$options.AllowSystemObjects = $false
$options.IncludeDatabaseContext = $false
$options.IncludeIfNotExists = $false
$options.ClusteredIndexes = $true
$options.NoCollation = $true
$options.Default = $true
$options.DriAll = $true
$options.Indexes = $true
$options.NonClusteredIndexes = $true
$options.IncludeHeaders = $false
$options.ToFileOnly = $true
$options.Permissions = $true
$options.ScriptDataCompression = $true
$options.ScriptDrops = $false
$options.AppendToFile = $false

#Set options for SMO.Scripter
$scr.Options = $options
if ($objectoption -eq 0 -or $objectoption -eq 1)
{
# script each table
foreach ($table in $db.Tables | where {$_.IsSystemObject -eq $false})
{
$tablefolder = $basefolder + "Tables\";
MakeDirectory ($tablefolder); #Check for folder, and create if needed
$tablefile = $tablefolder + $table.Name +".table.sql";
$options.FileName = $tablefile;
$scr.Options = $options;
$scr.Script($table);
}
}
if ($objectoption -eq 0 -or $objectoption -eq 2)
{
# Script table triggers (go into tables then triggers)
foreach ($table in $db.Tables)
{
foreach ($trigger in $Table.Triggers | where {$_.IsSystemObject -eq $false})
{
$triggerfolder = $basefolder + "Tables\Triggers\";
MakeDirectory ($triggerfolder); #Check for folder, and create if needed
$triggerfile = $triggerfolder + $trigger.name +".trigger.sql";
$options.FileName = $triggerfile;
$scr.Options = $options;
$scr.Script($trigger);
}
}
}
if ($objectoption -eq 0 -or $objectoption -eq 3)
{
# script each view
foreach ($view in $db.Views | where {$_.IsSystemObject -eq $false})
{
$viewfolder = $basefolder + "Views\";
MakeDirectory ($viewfolder); #Check for folder, and create if needed
$viewfile = $viewfolder + $view.Name +".view.sql";
$options.FileName = $viewfile;
$scr.Options = $options;
$scr.Script($view);
}
}
if ($objectoption -eq 0 -or $objectoption -eq 4)
{
# script each function
foreach ($function in $db.UserDefinedFunctions | where {$_.IsSystemObject -eq $false})
{
$functionfolder = $basefolder + "Programmability\Functions\";
MakeDirectory ($functionfolder); #Check for folder, and create if needed
$functionfile = $functionfolder + $function.Name +".function.sql";
$options.FileName = $functionfile ;
$scr.Options = $options;
$scr.Script($function);
}
}
if ($objectoption -eq 0 -or $objectoption -eq 5)
{
# script each stored procedure
foreach ($procedure in $db.StoredProcedures | where {$_.IsSystemObject -eq $false})
{
$procedurefolder = $basefolder + "Programmability\Stored Procedures\";
MakeDirectory ($procedurefolder); #Check for folder, and create if needed
$procedurefile = $procedurefolder + $procedure.Name +".proc.sql";
$options.FileName = $procedurefile ;
$scr.Options = $options;
$scr.Script($procedure);
}
}