Permalink
Cannot retrieve contributors at this time
Name already in use
A tag already exists with the provided branch name. Many Git commands accept both tag and branch names, so creating this branch may cause unexpected behavior. Are you sure you want to create this branch?
AliaSQL-Demo/schema_compare.ps1
Go to fileThis commit does not belong to any branch on this repository, and may belong to a fork outside of the repository.
83 lines (73 sloc)
3.33 KB
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
$databaseName = "Demo" | |
$base_dir = resolve-path . | |
$source_dir = "$base_dir\source" | |
$databaseScripts = "$source_dir\Database.Demo\" | |
$SqlPackage = "C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe" | |
$AliaSQL = "$base_dir\lib\AliaSQL\AliaSQL.exe" | |
$databaseName_Original = "$databaseName" + "_Original" | |
$databaseScriptsUpdate = "$databaseScripts\Scripts\Update" | |
#Powershell 2.0 can't do this so try another way | |
#$newScriptName = ((Get-ChildItem $databaseScriptsUpdate -filter "*.sql" | ForEach-Object {[int]$_.Name.Substring(0, 4)} | Sort-Object)[-1] + 1).ToString("0000-") + "$databaseName" + ".sql.temp" | |
$newScriptName = ([int]((gci $databaseScriptsUpdate -filter "*.sql" | sort CreationTime -desc | select -f 1).Name.Substring(0, 4)) + 1).ToString("0000-") + "$databaseName" + ".sql.temp" | |
write-host "Building original database..." | |
& $AliaSQL Rebuild .\sqlexpress "$databaseName_Original" "$databaseScripts\Scripts" | |
write-host "`n`nGenerating the diff script" | |
#generate the needed .dacpac (we'll delete it later) | |
& $SqlPackage /a:Extract /ssn:.\SQLEXPRESS /sdn:$databaseName /tf:$databaseScripts$databaseName.dacpac | |
#generate the diff script | |
& $SqlPackage /a:Script /op:$databaseScriptsUpdate\$newScriptName /p:DropObjectsNotInSource=true /p:BlockOnPossibleDataLoss=false /sf:$databaseScripts$databaseName.dacpac /tsn:.\SQLEXPRESS /tdn:"$databaseName_Original" | |
write-host "`n`nCleaning up generated script..." | |
$scriptLines = Get-Content $databaseScriptsUpdate\$newScriptName | |
Clear-Content $databaseScriptsUpdate\$newScriptName | |
$passedLastSqlCmdThing = $false | |
$skipBlock = $false | |
$blocksToSkip = "usd_AppliedDatabaseTestDataScript", "IX_usd_DateApplied" | |
$noDiff = $true | |
$noDiffLines = "", "GO", "PRINT N'Update complete.';" #these are the only lines left when there are no DB diffs | |
foreach($line in $scriptLines) | |
{ | |
#don't add anything until we get past the line USE [`$(DatabaseName)]; -- all the previous stuff should be sqlcmd/unncessary junk | |
if ($line -eq "USE [`$(DatabaseName)];") | |
{ | |
$passedLastSqlCmdThing = $true | |
} | |
#skip any blocks which contain any of the text in the skippable array | |
elseif ($blocksToSkip | Where-Object { $line.Contains($_) }) | |
{ | |
$skipBlock = $true | |
} | |
elseif ($passedLastSqlCmdThing -and -not $skipBlock) | |
{ | |
$newLine = "$line" | |
Add-Content $databaseScriptsUpdate\$newScriptName "$newLine" | |
if ($noDiff) | |
{ | |
#Powershell 2.0 can't do this so we revert to the hard way | |
#$noDiff = $noDiffLines.Contains($newLine) | |
$noDiff=$false | |
foreach($cline in $noDiffLines) | |
{ | |
if($cline -eq $newLine) | |
{ | |
$noDiff = $true | |
} | |
} | |
} | |
} | |
elseif ($line -eq "GO") | |
{ | |
$skipBlock = $false | |
} | |
} | |
write-host "Cleaning up temporary files and databases..." | |
& del $databaseScripts$databaseName.dacpac | |
& sqlcmd -S .\SQLEXPRESS -Q "DROP DATABASE $databaseName_Original" | |
if ($noDiff) | |
{ | |
Remove-Item $databaseScriptsUpdate\$newScriptName | |
write-host "No schema changes found for $databaseName" -foregroundcolor "green" | |
} | |
else | |
{ | |
write-host "Please validate the new script $databaseScriptsUpdate\$newScriptName is correct, then rename to .sql and add to the database project" -foregroundcolor "yellow" | |
} | |