Microsoft® SQL Server® 2012 Bible

796

Part V: Enterprise Data Management

$ScriptDrop.Options.WithDependencies = $False

$ScriptDrop.Options.Default = $False

$ScriptDrop.Options.DriPrimaryKey = $False

$ScriptDrop.Options.DriForeignKeys = $True

$TypeFolder=$ScriptThis.GetType().Name

if ((Test-Path -Path "$Path\$TypeFolder\Constraint") -eq "true") `

{"Scripting Out Constraint $ScriptThis"} `

else {

new-item -type directory -name "Constraint" -path "$Path\$TypeFolder"}

$ScriptFile = $ScriptThis -replace "\[|\]"

$ScriptDrop.Options.FileName = "" + $($Path) + "\" + $($TypeFolder) + "\

Constraint\" + $($ScriptFile) + ".SQL"

$ScriptDrop.Script($ScriptThis)

}

$Objects += $db.Schemas

$Objects += $db.Views

$Objects += $db.StoredProcedures

$Objects += $db.UserDefinedFunctions

foreach ($ScriptThis in $Objects | where {!($_.IsSystemObject)}) {

#Need to Add Some mkDirs for the different $Fldr=$ScriptThis.GetType().Name

$scriptr = new-object ('Microsoft.SqlServer.Management.Smo.Scripter')

($SMOserver)

$scriptr.Options.AppendToFile = $True

$scriptr.Options.AllowSystemObjects = $False

$scriptr.Options.ClusteredIndexes = $True

$scriptr.Options.DriAll = $False

$scriptr.Options.ScriptDrops = $False

$scriptr.Options.IncludeHeaders = $True

$scriptr.Options.ToFileOnly = $True

$scriptr.Options.Indexes = $True

$scriptr.Options.Permissions = $True

$scriptr.Options.WithDependencies = $False

<#This section builds folder structures.

Remove the date folder if you want to overwrite#>

$TypeFolder=$ScriptThis.GetType().Name

if ((Test-Path -Path "$Path\$TypeFolder") -eq "true") `

{"Scripting Out $TypeFolder $ScriptThis"} `

else {new-item -type directory -name "$TypeFolder"-path "$Path"}

$ScriptFile = $ScriptThis -replace "\[|\]"

$scriptr.Options.FileName = "$Path\$TypeFolder\$ScriptFile.SQL"

#This is where each object actually gets scripted one at a time.

LISTING 30-6 (continued)

c30.indd 796c30.indd 796 7/31/2012 9:46:24 AM7/31/2012 9:46:24 AM

http://www.it-ebooks.info