PowerShell.PowerLibrary.SqlScripter.psm1
|
#region Imports Import-Module -Assembly ([System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")) -ErrorAction SilentlyContinue; Import-Module -Assembly ([System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfoExtended")) -ErrorAction SilentlyContinue; Import-Module -Assembly ([System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")) -ErrorAction SilentlyContinue; Import-Module -Assembly ([System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended")) -ErrorAction SilentlyContinue; Import-Module -Assembly ([System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.Sdk.Sfc")) -ErrorAction SilentlyContinue; #endregion #region Variables [Microsoft.SqlServer.Management.Smo.Server]$Server = $null; [Microsoft.SqlServer.Management.Smo.Database]$Database = $null; $ExcludedUrnTypes = [System.Collections.Specialized.StringCollection]::new(); $ExcludedProperties = [System.Collections.Specialized.StringCollection]::new(); $WalkableTypes = [System.Collections.Specialized.StringCollection]::new(); $TypesWeight = [System.Collections.Generic.Dictionary[string,int]]::new(); [int]$MaxWeightThreshold = 1000000000; [int]$MinWeightThreshold = -($MaxWeightThreshold); [System.Globalization.CompareOptions[]]$UrnCompareOptions = @([System.Globalization.CompareOptions]::IgnoreCase); [string[]]$IncludedTables; [string[]]$ExcludedTables; #endregion #region Functions #region Initialize Function <# Initializes the Module #> FUNCTION Initialize-SqlScripter { #region Excluded Urn Types $Script:ExcludedUrnTypes.Clear(); $__ = $Script:ExcludedUrnTypes.Add("UnresolvedEntity"); #endregion #region Excluded Properties $Script:ExcludedProperties.Clear(); $___ = $Script:ExcludedProperties.Add("Federations"); $___ = $Script:ExcludedProperties.Add("ActiveDirectory"); $___ = $Script:ExcludedProperties.Add("AvailabilityDatabaseSynchronizationState"); $___ = $Script:ExcludedProperties.Add("IsDbManager"); $___ = $Script:ExcludedProperties.Add("IsFederationMember"); $___ = $Script:ExcludedProperties.Add("IsLoginManager"); $___ = $Script:ExcludedProperties.Add("AzureServiceObjective"); $___ = $Script:ExcludedProperties.Add("AzureEdition"); $___ = $Script:ExcludedProperties.Add("AutoCreateIncrementalStatisticsEnabled"); $___ = $Script:ExcludedProperties.Add("DelayedDurability"); $___ = $Script:ExcludedProperties.Add("HasFileInCloud"); $___ = $Script:ExcludedProperties.Add("HasMemoryOptimizedObjects"); $___ = $Script:ExcludedProperties.Add("MemoryAllocatedToMemoryOptimizedObjectsInKB"); $___ = $Script:ExcludedProperties.Add("MemoryUsedByMemoryOptimizedObjectsInKB"); #endregion #region WalkableTypes $Script:WalkableTypes.Clear(); $___ = $Script:WalkableTypes.Add("UserDefinedFunction"); $___ = $Script:WalkableTypes.Add("View"); $___ = $Script:WalkableTypes.Add("Table"); $___ = $Script:WalkableTypes.Add("StoredProcedure"); $___ = $Script:WalkableTypes.Add("Default"); $___ = $Script:WalkableTypes.Add("Rule"); $___ = $Script:WalkableTypes.Add("Trigger"); $___ = $Script:WalkableTypes.Add("UserDefinedAggregate"); $___ = $Script:WalkableTypes.Add("Synonym"); $___ = $Script:WalkableTypes.Add("Sequence"); $___ = $Script:WalkableTypes.Add("UserDefinedDataType"); $___ = $Script:WalkableTypes.Add("XmlSchemaCollection"); $___ = $Script:WalkableTypes.Add("UserDefinedType"); $___ = $Script:WalkableTypes.Add("UserDefinedTableType"); $___ = $Script:WalkableTypes.Add("PartitionScheme"); $___ = $Script:WalkableTypes.Add("PartitionFunction"); $___ = $Script:WalkableTypes.Add("DdlTrigger"); $___ = $Script:WalkableTypes.Add("PlanGuide"); $___ = $Script:WalkableTypes.Add("SqlAssembly"); $___ = $Script:WalkableTypes.Add("UnresolvedEntity"); #endregion #region TypesWeight $Script:TypesWeight.Clear(); $Script:TypesWeight.Add("Schema", -1000000020 ); $Script:TypesWeight.Add("fulltextcatalog", -1000000010 ); $Script:TypesWeight.Add("UserDefinedAggregate", 0 ); $Script:TypesWeight.Add("UserDefinedDataType", 0 ); $Script:TypesWeight.Add("UserDefinedFunction", 0 ); $Script:TypesWeight.Add("UserDefinedTableType", 0 ); $Script:TypesWeight.Add("UserDefinedType", 0 ); $Script:TypesWeight.Add("Table", 1000 ); $Script:TypesWeight.Add("ForeignKey", 1001 ); $Script:TypesWeight.Add("StoredProcedure", 1000000005 ); $Script:TypesWeight.Add("MessageType", 1000000010 ); $Script:TypesWeight.Add("ServiceContract", 1000000020 ); $Script:TypesWeight.Add("ServiceQueue", 1000000030 ); $Script:TypesWeight.Add("BrokerService", 1000000040 ); #endregion } #endregion #region Set-Server Function <# Set-Server #> FUNCTION Set-Server { #region Parameters [CmdletBinding()] param ( [Parameter(Mandatory = $false, ValueFromPipeline = $true, Position = 0)] [string] $ServerName = "(local)", [Parameter(Mandatory = $false, ValueFromPipeline = $true, Position = 1)] [string] $UserName, [Parameter(Mandatory = $false, ValueFromPipeline = $true, Position = 2)] [string] $Password ); #endregion IF($Script:Server -ne $null){ RETURN; } [Microsoft.SqlServer.Management.Common.ServerConnection]$Connection = $null; IF([string]::IsNullOrWhiteSpace($UserName)) { $Connection = [Microsoft.SqlServer.Management.Common.ServerConnection]::new($ServerName); } ELSE { $Connection = [Microsoft.SqlServer.Management.Common.ServerConnection]::new($ServerName, $UserName, $Password); } $Script:Server = [Microsoft.SqlServer.Management.Smo.Server]::new($Connection); } #endregion #region Set-Database Function <# Set-Database #> FUNCTION Set-Database { #region Parameters [CmdletBinding()] param ( [Parameter(Mandatory = $true, ValueFromPipeline = $true, Position = 0)] [ValidateNotNullOrEmpty()] [string] $DatabaseName ); #endregion IF($Script:Server -eq $null) { throw 'Server is not set'; } $Script:Database = $Server.Databases[$DatabaseName]; IF($Script:Database -eq $null) { throw "Database $DatabaseName does not exist."; } } #endregion #region Get-TypeWeight Function <# Gets the Type Weight Index #> FUNCTION Get-TypeWeight { #region Parameters [CmdletBinding()] param ( [Parameter(Mandatory = $false, ValueFromPipeline = $true, Position = 0)] [string] $Type ); #endregion IF([string]::IsNullOrWhiteSpace($Type)) { RETURN $null; } [int]$Value = 0; IF($TypesWeight.TryGetValue($Type, [ref]$Value)) { RETURN $Value; } ELSE { $Weight = $TypesWeight.GetEnumerator() | Where-Object { $_.Key.Equals($Type, [System.StringComparison]::InvariantCultureIgnoreCase); } | Select -First 1; IF($Weight -ne $null) { RETURN $Weight.Value; } RETURN $null; } RETURN $null; } #endregion #region Test-TypeIfExcluded <# Test-TypeIfExcluded #> FUNCTION Test-TypeIfExcluded { #region Parameters [CmdletBinding()] [OutputType([bool])] param ( [Parameter(Mandatory = $false, ValueFromPipeline = $true, Position = 0)] [string] $Type ); #endregion IF([string]::IsNullOrWhiteSpace($Type)) { RETURN $true; } RETURN $ExcludedUrnTypes.Contains($Type); } #endregion #region Test-SystemObject <# Test-SystemObject #> FUNCTION Test-SystemObject { [CmdletBinding()] [OutputType([bool])] param ( [Parameter(Mandatory = $true, ValueFromPipeline = $true, Position = 0)] [Microsoft.SqlServer.Management.Smo.SqlSmoObject] $Smo ); TRY { IF($Smo.Urn.Type.Equals('fulltextcatalog', [System.StringComparison]::InvariantCultureIgnoreCase)) { return $false; } $ScriptSchema = $Smo.Urn.GetAttribute("Schema"); if( ![string]::IsNullOrWhiteSpace($ScriptSchema) -and ( $ScriptSchema.Equals("INFORMATION_SCHEMA", [System.StringComparison]::InvariantCultureIgnoreCase) -or $ScriptSchema.Equals("sys", [System.StringComparison]::InvariantCultureIgnoreCase) ) ) { return $true; } $Type = $Smo.GetType(); $PI = $Type.GetProperty("IsSystemObject", [bool]); IF($PI -eq $null) { $PI = $Type.GetProperty("IsUserDefined", [bool]); IF($PI -eq $null) { # Assume it is a system object, unless its type starts with 'UserDefined'. return !$Type.Name.StartsWith("UserDefined", [System.StringComparison]::InvariantCultureIgnoreCase); } # If the object is not user-defined, then it is a system object. return ![bool]::Parse($PI.GetValue($Smo).ToString()); } # Return the value of IsSystemObject return [bool]::Parse($PI.GetValue($Smo).ToString()); } CATCH { return $true; } } #endregion #region Test-TableIfIncluded <# Test-TableIfIncluded #> FUNCTION Test-TableIfIncluded { #region Parameters [CmdletBinding()] [OutputType([bool])] param ( [Parameter(Mandatory = $true, ValueFromPipeline = $true, Position = 0)] [ValidateNotNullOrEmpty()] [string] $TableName ); #endregion $Included = $true; IF($IncludedTables.Length -gt 0) { $__ = $IncludedTables | ?{ $_.Equals($TableName, [System.StringComparison]::InvariantCultureIgnoreCase); } | Select -First 1; IF(![string]::IsNullOrWhiteSpace($__)) { $Included = $true; } ELSE { $Included = $false; } } IF($Included -eq $true -and $ExcludedTables.Length -gt 0) { $__ = $ExcludedTables | ?{ $_.Equals($TableName, [System.StringComparison]::InvariantCultureIgnoreCase); } | Select -First 1; IF(![string]::IsNullOrWhiteSpace($__)) { $Included = $false; } } RETURN $Included; } #endregion #region Export-Urns <# Export-Urns #> FUNCTION Export-Urns { #region Parameters [CmdletBinding()] [OutputType([System.Collections.Generic.List[Microsoft.SqlServer.Management.Sdk.Sfc.Urn]])] param ( [Parameter(Mandatory = $true, ValueFromPipeline = $true, Position = 0)] [object] $Object ); #endregion $Value = [System.Collections.Generic.List[Microsoft.SqlServer.Management.Sdk.Sfc.Urn]]::new(); IF($Object -eq $null) { RETURN $Value; } foreach ($PI in $Object.GetType().GetProperties()) { IF($PI -eq $null -or $ExcludedProperties.Contains($PI.Name)) { continue; } $PV = $null; TRY { $PV = $PI.GetValue($Object); } CATCH { $PV = $null; } IF($PV -ne $null) { $Collection = $PV -as [System.Collections.ICollection]; IF($Collection -ne $null) { foreach($Item in $Collection) { $Smo = $Item -as [Microsoft.SqlServer.Management.Smo.SqlSmoObject]; if($Smo -ne $null -and !(Test-SystemObject -Smo $Smo)) { $Table = $Smo -as [Microsoft.SqlServer.Management.Smo.Table]; IF($Table -ne $null -and $Table.ForeignKeys -ne $null -and $Table.ForeignKeys.Count -gt 0) { foreach ($FK in $Table.ForeignKeys) { $FK_Smo = $FK -as [Microsoft.SqlServer.Management.Smo.SqlSmoObject]; IF($FK_Smo -ne $null) { $Value.Add($FK_Smo.Urn); } } } $Value.Add($Smo.Urn); } } } } } Return $Value; } #endregion #region Get-Urns <# Get-Urns #> FUNCTION Get-Urns { #region Parameters [CmdletBinding()] param ( [Parameter(Mandatory = $false, ValueFromPipeline = $true)] [string] [ValidateSet('Database', 'ServiceBroker', 'Both')] $UrnScope = "Both" ); #endregion $Range = $null; $Value = [System.Collections.Generic.List[Microsoft.SqlServer.Management.Sdk.Sfc.Urn]]::new(); IF($Database -eq $null) { throw 'Database is not set.'; } IF($UrnScope -eq 'Database' -or $UrnScope -eq 'Both') { $Range = (Export-Urns $Database) -as [System.Collections.Generic.List[Microsoft.SqlServer.Management.Sdk.Sfc.Urn]]; IF($Range -ne $null) { $Value.AddRange($Range); } } IF($UrnScope -eq 'ServiceBroker' -or $UrnScope -eq 'Both') { $Range = (Export-Urns $Database.ServiceBroker) -as [System.Collections.Generic.List[Microsoft.SqlServer.Management.Sdk.Sfc.Urn]]; IF($Range -ne $null) { $Value.AddRange($Range); } } RETURN $Value; } #endregion #region Write-Script <# .SYNOPSIS Script SQL URN Object .DESCRIPTION This function Writes Schema & Data Scripts. This function accepts pipeline output. .PARAMETER Input URN Object(s) of type [Microsoft.SqlServer.Management.Sdk.Sfc.Urn[]] .PARAMETER StreamWriter System IO Stream Writer which handles scriptind DATA. .PARAMETER ScriptingOptions Microsoft SqlServer Management Smo ScriptingOptions .PARAMETER ScriptDrop Switch to toggle scripting Drop|Delete .PARAMETER ScriptSchema Switch to toggle scripting Schema .PARAMETER ScriptData Switch to toggle scripting Data .EXAMPLE $Descending.Value | Write-Script -StreamWriter $StreamWriter -ScriptingOptions $ScriptingOptions -ScriptDrop:$true -ScriptSchema:$ScriptSchema -ScriptData:$ScriptData; .NOTES ----Ahmad A. Ousman----. .LINK #> FUNCTION Write-Script { #region Parameters [CmdletBinding()] param ( [Parameter(Mandatory = $true, ValueFromPipeline = $true)] [Microsoft.SqlServer.Management.Sdk.Sfc.Urn[]] $Input, [Parameter(Mandatory = $false, ValueFromPipeline = $true)] [System.IO.StreamWriter] $StreamWriter, [Parameter(Mandatory = $true, ValueFromPipeline = $true)] [Microsoft.SqlServer.Management.Smo.ScriptingOptions] $ScriptingOptions, [Parameter(Mandatory = $false, ValueFromPipeline = $true)] [switch] $ScriptDrop = $false, [Parameter(Mandatory = $false, ValueFromPipeline = $true)] [switch] $ScriptSchema = $false, [Parameter(Mandatory = $false, ValueFromPipeline = $true)] [switch] $ScriptData = $false, [Parameter(Mandatory = $false, ValueFromPipeline = $true)] [switch] $Silent = $false ); #endregion BEGIN { IF($ScriptData -and $StreamWriter -eq $null) { Write-Warning "Stream Writer is NULL.`nExpect no Data Script."; } $ScriptingType = $(IF($ScriptDrop.IsPresent){'Drop|Delete'}ELSE{'Create|Insert'}); Write-Host "Scripting Process Started ($ScriptingType)" -ForegroundColor Cyan; $Scripter = [Microsoft.SqlServer.Management.Smo.Scripter]::new($Server); } PROCESS { $Scripter.Options = [Microsoft.SqlServer.Management.Smo.ScriptingOptions]::new($ScriptingOptions); $Scripter.Options.ScriptDrops = $ScriptDrop.IsPresent; $Scripter.Options.IncludeIfNotExists = $ScriptDrop.IsPresent; FOREACH($Urn in $Input) { IF(!(Test-TypeIfExcluded -Type $Urn.Type)) { $UrnName = $Urn.GetAttribute('Name'); IF(!$Silent) { Write-Host "Scripting URN ($ScriptingType)..." -ForegroundColor Yellow; Write-Host "`tType:`t" -NoNewline -ForegroundColor DarkGreen; Write-Host $Urn.Type -ForegroundColor White; Write-Host "`tName:`t" -NoNewline -ForegroundColor DarkGreen; Write-Host $UrnName -ForegroundColor White; Write-Host; } IF($ScriptSchema.IsPresent) { TRY { $__ = $Scripter.Script(@($Urn)); #FOREACH($SQL in $__) #{ # $StreamWriter.WriteLine($SQL); #} } CATCH [System.Exception] { Write-Message "An Error has occured while scripting URN of Type (" -NoNewline -ForegroundColor Red; Write-Host $Urn.Type -NoNewline -ForegroundColor White; Write-Host ")" -ForegroundColor Red; Write-Error -Exception $_.Exception -Message $_.Exception.Message; } } IF($Urn.Type.Equals('table', [System.StringComparison]::InvariantCultureIgnoreCase)) { $TableName = $UrnName; $Table = $Server.GetSmoObject($Urn) -as [Microsoft.SqlServer.Management.Smo.Table] if($Table -ne $null -and ![string]::IsNullOrWhiteSpace($TableName)) { IF($Table.FullTextIndex -ne $null -and $ScriptSchema.IsPresent) { TRY { $__ = $Scripter.Script(@($Table.FullTextIndex)); #FOREACH($SQL in $__) #{ # $StreamWriter.WriteLine($SQL); #} } CATCH [System.Exception] { Write-Message "An Error has occured while scripting FullTextIndex of Table (" -NoNewline -ForegroundColor Red; Write-Host $TableName -NoNewline -ForegroundColor White; Write-Host ")" -ForegroundColor Red; Write-Error -Exception $_.Exception -Message $_.Exception.Message; } } IF($ScriptData.IsPresent -and $StreamWriter -ne $null -and (Test-TableIfIncluded $TableName)) { $Scripter.Options.ScriptSchema = $false; $Scripter.Options.ScriptData = $true; $Scripter.Options.FileName = $null; $Scripter.Options.ToFileOnly = $false; $Scripter.PrefetchObjects = $false; IF($Table.FullTextIndex -ne $null) { $StreamWriter.WriteLine("ALTER FULLTEXT INDEX ON [$TableName] DISABLE"); $StreamWriter.WriteLine("GO"); } $FlushIndex = 0; FOREACH($SQL in $Scripter.EnumScript(@($Urn))) { $StreamWriter.WriteLine($SQL); $StreamWriter.WriteLine("GO"); $FlushIndex++; IF($FlushIndex % 100 -eq 0) { $StreamWriter.Flush(); } } IF($Table.FullTextIndex -ne $null) { $StreamWriter.WriteLine("ALTER FULLTEXT INDEX ON [$TableName] ENABLE"); $StreamWriter.WriteLine("GO"); $StreamWriter.WriteLine("ALTER FULLTEXT INDEX ON [$TableName] START FULL POPULATION"); $StreamWriter.WriteLine("GO"); } $StreamWriter.Flush(); [System.GC]::Collect(4, [System.GCCollectionMode]::Forced, $true); } } } } } } END { Write-Host "Scripting Process Ended ($ScriptingType)" -ForegroundColor DarkCyan; Write-Host; } } #endregion #region Export-SQL <# Export SQL #> FUNCTION Export-SQL { <# .Synopsis Use this Method to Export SQL Script including Schema, Data, and Broker Services. .DESCRIPTION This Function Script Database Schema, Data, and Broker Services. .PARAMETER ServerName Server Name. .PARAMETER UserName SQL User Name. .PARAMETER Password SQL User Name Password. .PARAMETER TablesIncluded List of tables to be included. .PARAMETER TablesExcluded List of Tables to be excluded. .PARAMETER DatabaseName SQL Database Name. .PARAMETER Scope 'Database', 'ServiceBroker', 'Both' .PARAMETER SchemaFilePath Path to the Generated Schema File. .PARAMETER DataFilePath Path to the Generated Data File. .PARAMETER ScriptingOptions Of Type [Microsoft.SqlServer.Management.Smo.ScriptingOptions] .PARAMETER ScriptDrop Switch to enable/disable scripting Drop. .PARAMETER Compress Switch to enable/disable Compression. #> #region Parameters [CmdletBinding()] param ( [Parameter(Mandatory = $false, ValueFromPipeline = $true)] [string] $ServerName = "(local)", [Parameter(Mandatory = $false, ValueFromPipeline = $true)] [string] $UserName, [Parameter(Mandatory = $false, ValueFromPipeline = $true)] [string] $Password, [Parameter(Mandatory = $false, ValueFromPipeline = $true)] [string[]] $TablesIncluded, [Parameter(Mandatory = $false, ValueFromPipeline = $true)] [string[]] $TablesExcluded, [Parameter(Mandatory = $true, ValueFromPipeline = $true)] [ValidateNotNullOrEmpty()] [string] $DatabaseName, [Parameter(Mandatory = $false, ValueFromPipeline = $true)] [string] [ValidateSet('Database', 'ServiceBroker', 'Both')] $Scope = "Both", [Parameter(Mandatory = $false, ValueFromPipeline = $true)] [string] [ValidateNotNullOrEmpty()] $SchemaFilePath, [Parameter(Mandatory = $false, ValueFromPipeline = $true)] [string] [ValidateNotNullOrEmpty()] $DataFilePath, [Parameter(Mandatory = $false, ValueFromPipeline = $true)] [Microsoft.SqlServer.Management.Smo.ScriptingOptions] $ScriptingOptions, [Parameter(Mandatory = $false, ValueFromPipeline = $true)] [switch] $ScriptDrop = $false, [Parameter(Mandatory = $false, ValueFromPipeline = $true)] [switch] [Alias('Zip')] $Compress = $false, [Parameter(Mandatory = $false, ValueFromPipeline = $true)] [switch] $Silent = $false ); #endregion $SW = [System.Diagnostics.Stopwatch]::new(); $SW.Start(); #region Initialize ScriptingOptions IF($ScriptingOptions -eq $null) { $ScriptingOptions = New-Object "Microsoft.SqlServer.Management.Smo.ScriptingOptions"; $ScriptingOptions.AppendToFile = $true; $ScriptingOptions.ClusteredIndexes = $true; $ScriptingOptions.DriIndexes = $true; $ScriptingOptions.DriPrimaryKey = $true; $ScriptingOptions.DriUniqueKeys = $true; $ScriptingOptions.DriClustered = $true; $ScriptingOptions.DriDefaults = $true; $ScriptingOptions.DriNonClustered = $true; $ScriptingOptions.Encoding = [System.Text.Encoding]::UTF8; $ScriptingOptions.ExtendedProperties = $true; $ScriptingOptions.IncludeHeaders = $false; $ScriptingOptions.IncludeIfNotExists = $false; $ScriptingOptions.Indexes = $true; $ScriptingOptions.SchemaQualify = $true; $ScriptingOptions.TargetDatabaseEngineType = [Microsoft.SqlServer.Management.Common.DatabaseEngineType]::Standalone; $ScriptingOptions.TargetServerVersion = [Microsoft.SqlServer.Management.Smo.SqlServerVersion]::Version105; $ScriptingOptions.ToFileOnly = $true; } #endregion $Script:IncludedTables = $TablesIncluded; $Script:ExcludedTables = $TablesExcluded; Set-Server -ServerName $ServerName -UserName $UserName -Password $Password; Set-Database -DatabaseName $DatabaseName; [System.IO.StreamWriter]$StreamWriter = $null; $ScriptData = ![string]::IsNullOrWhiteSpace($DataFilePath); $ScriptSchema = ![string]::IsNullOrWhiteSpace($SchemaFilePath); IF($ScriptSchema) { $ScriptingOptions.FileName = $SchemaFilePath; IF([System.IO.File]::Exists($SchemaFilePath)) { [System.IO.File]::Delete($SchemaFilePath); } } IF($ScriptData) { IF([System.IO.File]::Exists($DataFilePath)) { [System.IO.File]::Delete($DataFilePath); } IF($Compress.IsPresent) { $DataFilePath = [string]::Concat($DataFilePath, '.gz'); } IF([System.IO.File]::Exists($DataFilePath)) { [System.IO.File]::Delete($DataFilePath); } $FileStream = [System.IO.File]::Create($DataFilePath, 32 * 1024 * 1024, [System.IO.FileOptions]::WriteThrough); $Stream = $FileStream; IF($Compress.IsPresent) { $Stream = [System.IO.Compression.GZipStream]::new($FileStream, [System.IO.Compression.CompressionLevel]::Optimal); } $StreamWriter = [System.IO.StreamWriter]::new($Stream, [System.Text.Encoding]::UTF8); } IF(!$ScriptData -and !$ScriptSchema) { throw 'Please Specify Shema|Data file path.'; } $Unsorted = Get-Urns -UrnScope $Scope; $WalkableTypes = [Microsoft.SqlServer.Management.Smo.UrnCollection]::new(); $NonWalkableTypes = [Microsoft.SqlServer.Management.Smo.UrnCollection]::new(); $DependencyWalker = [Microsoft.SqlServer.Management.Smo.DependencyWalker]::new($Server); $Ordered = [System.Collections.Generic.List[System.Collections.Generic.KeyValuePair[[long], [Microsoft.SqlServer.Management.Sdk.Sfc.Urn]]]]::new(); FOREACH($SmoUrn in $Unsorted) { IF($Script:WalkableTypes.Contains($SmoUrn.Type)) { $__ = $WalkableTypes.Add($SmoUrn); } ELSE { $__ = $NonWalkableTypes.Add($SmoUrn); } } [long]$UniqueIndexer = 0; IF($WalkableTypes.Count -gt 0) { $DependecyTree = $DependencyWalker.DiscoverDependencies($WalkableTypes, [Microsoft.SqlServer.Management.Smo.DependencyType]::Parents); $WalkedDependencies = $DependencyWalker.WalkDependencies($DependecyTree); IF($WalkedDependencies -ne $null -and $WalkedDependencies.Count -gt 0) { foreach($WalkedDependency in $WalkedDependencies) { $Index = Get-TypeWeight $WalkedDependency.Urn.Type; IF($Index -eq $null) { $Index = 0; } $Index += $UniqueIndexer; $__ = $Ordered.Add([System.Collections.Generic.KeyValuePair[[long], [Microsoft.SqlServer.Management.Sdk.Sfc.Urn]]]::new($Index, $WalkedDependency.Urn)); $UniqueIndexer++; } } } IF($NonWalkableTypes.Count -gt 0) { foreach($NonWalkableType in $NonWalkableTypes) { $Index = Get-TypeWeight $NonWalkableType.Type; IF($Index -eq $null) { $Index = 0; } $Index += $UniqueIndexer; $__ = $Ordered.Add([System.Collections.Generic.KeyValuePair[[long], [Microsoft.SqlServer.Management.Sdk.Sfc.Urn]]]::new($Index, $NonWalkableType)); $UniqueIndexer++; } } IF($Ordered.Count -gt 0) { IF($ScriptData -and $StreamWriter -ne $null) { $StreamWriter.WriteLine("SET NOCOUNT ON"); $StreamWriter.WriteLine("GO"); } #region Script DROP & DELETE IF($ScriptDrop.IsPresent) { $Descending = $Ordered | Sort-Object { [long]$_.Key } -Descending:$true; $Descending.Value | Write-Script -StreamWriter $StreamWriter -ScriptingOptions $ScriptingOptions -ScriptDrop:$true -ScriptSchema:$ScriptSchema -ScriptData:$ScriptData -Silent:$Silent; } #endregion #region Script Create & Insert $Ascending = $Ordered | Sort-Object { [long]$_.Key } -Descending:$false; $Ascending.Value | Write-Script -StreamWriter $StreamWriter -ScriptingOptions $ScriptingOptions -ScriptDrop:$false -ScriptSchema:$ScriptSchema -ScriptData:$ScriptData -Silent:$Silent; #endregion } IF($StreamWriter -ne $null) { $StreamWriter.Flush(); $StreamWriter.Close(); $StreamWriter.Dispose(); $StreamWriter = $null; } $SW.Stop(); Write-Host; Write-Host "Days: " -NoNewline -ForegroundColor Yellow; Write-Host $SW.Elapsed.Days -ForegroundColor White; Write-Host "Hours: " -NoNewline -ForegroundColor Yellow; Write-Host $SW.Elapsed.Hours -ForegroundColor White; Write-Host "Minutes: " -NoNewline -ForegroundColor Yellow; Write-Host $SW.Elapsed.Minutes -ForegroundColor White; Write-Host "Seconds: " -NoNewline -ForegroundColor Yellow; Write-Host $SW.Elapsed.Seconds -ForegroundColor White; Write-Host "Milliseconds: " -NoNewline -ForegroundColor Yellow; Write-Host $SW.Elapsed.Milliseconds -ForegroundColor White; } #endregion #endregion #region Process Initialize-SqlScripter; #endregion |