You are hereSQL Server 2005 DBA / PowerShell Script to Export SQL Schema Definition

PowerShell Script to Export SQL Schema Definition


By sqlguychuck - Posted on 15 September 2009

No votes yet

Ganesh Kaliaperumal created a script to export schema to files for us to easily compare with WinMerge. Check out the options and modify as needed.
Call from powershell command line like so:
.\GenerateScript.ps1 "Servername" "Adventureworks" "c:\scripts\powershell\"
#Start file
Set-ExecutionPolicy RemoteSigned
#Set-ExecutionPolicy -ExecutionPolicy:Unrestricted -Scope:LocalMachine
function GenerateDBScript([string]$serverName, [string]$dbname, [string]$scriptpath)
{
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
[System.Reflection.Assembly]::LoadWithPartialName("System.Data") | out-null
#$error.clear()
#$erroractionpreference = "Continue"
$srv = new-object "Microsoft.SqlServer.Management.SMO.Server" $serverName
$srv.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.View], "IsSystemObject")
$db = New-Object "Microsoft.SqlServer.Management.SMO.Database"
$db = $srv.Databases[$dbname]
$scr = New-Object "Microsoft.SqlServer.Management.Smo.Scripter"
$deptype = New-Object "Microsoft.SqlServer.Management.Smo.DependencyType"
$scr.Server = $srv
$options = New-Object "Microsoft.SqlServer.Management.SMO.ScriptingOptions"
$options.AllowSystemObjects = $false
$options.IncludeDatabaseContext = $true
$options.IncludeIfNotExists = $true
$options.ClusteredIndexes = $true
$options.Default = $true
$options.DriAll = $true
$options.Indexes = $true
$options.NonClusteredIndexes = $true
$options.IncludeHeaders = $true
$options.ToFileOnly = $true
$options.AppendToFile = $true
#Set options for SMO.Scripter
$scr.Options = $options
#Tables
$options.FileName = $scriptpath + $dbname + "_Tables_Script.sql"
$scr.Script($db.Tables)
#Views
$options.FileName = $scriptpath + $dbname + "_Views_Script.sql"
$views = $db.Views | where {$_.IsSystemObject -eq $false}
if ($views -ne $null)
{
 $scr.Script($views)
}
#StoredProcedures
$options.FileName = $scriptpath + $dbname + "_Procs_Script.sql"
$StoredProcedures = $db.StoredProcedures | where {$_.IsSystemObject -eq $false}
if ($StoredProcedures -ne $null)
{
 $scr.Script($StoredProcedures)
}
#Functions
$options.FileName = $scriptpath + $dbname + "_Functions_Script.sql"
$UserDefinedFunctions = $db.UserDefinedFunctions | where {$_.IsSystemObject -eq $false}
if ($UserDefinedFunctions -ne $null)
{
 $scr.Script($UserDefinedFunctions)
}
#Triggers
$options.FileName = $scriptpath + $dbname + "_Triggers_Script.sql"
foreach($tables in $db.Tables)
{
 foreach($trigger in $tables.Triggers)
  {
   $scr.Script($trigger)
  }
}
#DBTriggers
$options.FileName = $scriptpath + $dbname + "_DBTriggers_Script.sql"
$DBTriggers = $db.Triggers
if ($DBTriggers -ne $null)
{
 $scr.Script($DBTriggers)
}
}
GenerateDBScript $args[0] $args[1] $args[2]
 

Rate This

No votes yet