Skip to content

Instantly share code, notes, and snippets.

@anderssonjohan
Created September 29, 2014 08:16
Show Gist options
  • Save anderssonjohan/b612f0fe036e75fe76d3 to your computer and use it in GitHub Desktop.
Save anderssonjohan/b612f0fe036e75fe76d3 to your computer and use it in GitHub Desktop.
Sample script we use at RemoteX to generate a SQL file with the database schema. We call this script each time we make a change script (migration), which will be used when setting up new DB instances rather than executing all the change scripts.
param(
[parameter(mandatory=$false)]
$sqlInstance = "(local)",
[parameter(mandatory=$false)]
$database,
[switch] $silent
)
$csFilePath = join-path -resolve $PSScriptRoot "Services/RESTService/Service/connectionstrings.config"
$CreateTablesSql = join-path -resolve $PSScriptRoot "Setup/SetupSkeleton/DB/Schema/CreateScripts/CreateTables.sql"
$CreateSearchTablesSql = join-path -resolve $PSScriptRoot "Setup/SetupSkeleton/DB/Schema/CreateScripts/CreateSearchTables.sql"
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.Sdk.Sfc") | out-null
function ScriptDatabase( [string] $sqlInstance, [string] $database, [string[]] $excludeSchema, [string[]] $includeSchema ) {
$srv = new-object Microsoft.SqlServer.Management.Smo.Server $sqlInstance
$db = $srv.Databases[$database]
$scrp = new-object Microsoft.SqlServer.Management.Smo.Scripter $srv
$scrp.Options.ScriptSchema = $true
$scrp.Options.SchemaQualify = $true
$scrp.Options.WithDependencies = $true
$scrp.Options.Indexes = $true # To include indexes
$scrp.Options.FullTextIndexes = $true # To include full text indexes
$scrp.Options.DriAllConstraints = $true # to include referential constraints in the script
$scrp.Options.DriDefaults = $true
$scrp.Options.AnsiPadding = $false
$scrp.Options.ScriptBatchTerminator = $true
$scrp.Options.ExtendedProperties = $true
$scrp.PrefetchObjects = $true # some sources suggest this may speed things up
$schemas = @()
$urns = @()
# Schemas cannot be scripted using the options above without getting an scripting error
# We create a new options object here for that
$schemaOptions = (new-object Microsoft.SqlServer.Management.Smo.Scripter $srv).Options
$schemaOptions.IncludeHeaders = $false
$schemaOptions.ScriptOwner = $true
$db.Schemas | %{
if (!$_.IsSystemObject -and (($excludeSchema -and $excludeSchema -notcontains $_.Name ) -or ($includeSchema -and $includeSchema -contains $_.Name)) )
{
$schemas += $_.Script($schemaOptions)
if(!$silent) { Write-Host -nonewline "." }
}
}
$db.Tables + $db.Views + $db.StoredProcedures + $db.UserDefinedFunctions | %{
if (!$_.IsSystemObject -and (($excludeSchema -and $excludeSchema -notcontains $_.Schema ) -or ($includeSchema -and $includeSchema -contains $_.Schema)) )
{
$urns += $_.Urn
if(!$silent) { Write-Host -nonewline "." }
}
}
$builder = new-object System.Text.StringBuilder
$schemas + $scrp.Script($urns) | %{
$builder.AppendLine($_) | out-null
$builder.AppendLine("GO") | out-null
}
return $builder.ToString()
}
function connectionstring_hash( $connectionString ) {
$connectionString.Split(';') | %{
$pair = $_.Split( '=' )
new-object psobject -property @{ "Key" = $pair[0]; "Value" = $pair[1] }
} | group -AsHashTable Key
}
if( !( $sqlInstance -and $database )) {
[xml]$config = gc $csFilePath
$csmap = connectionstring_hash $config.connectionStrings.add.connectionString
$sqlInstance = $csmap["Data Source"].Value
$database = $csmap["Initial Catalog"].Value
}
Write-Host -foregroundcolor yellow "Using SQL server $sqlInstance and database $database"
Write-Host "Generating $CreateTablesSql"
sc -path $CreateTablesSql -value (ScriptDatabase -sqlInstance $sqlInstance -database $database -excludeSchema "Search" )
Write-Host ""
Write-Host "Generating $CreateSearchTablesSql"
sc -path $CreateSearchTablesSql -value (ScriptDatabase -sqlInstance $sqlInstance -database $database -includeSchema "Search")
Write-Host ""
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment