A Quick Diff Checker for SQL Server Databases
Compare the schemas of two SQL Server databases using SQL Compare command line then quickly produce a diff report showing you immediately which tables, views and functions have changed.
One of the most elementary requirements when you’re working with a database is to quickly find out what’s changed. You want to compare two databases for differences and quickly generate a “diff report” that shows you, immediately, a list of objects that are in one database but not the other, or are in both but are different.
Database schema differencing can show you how two branches compare so that you can merge them appropriately. Alternatively, it could be that you just need to see what has changed in the main developer branch since the last release, or to check what changes a migration file did, or to make sure that the database that was delivered is the same as what is in source control.
The old Diff tools compared the contents of two files and displayed the differences, line by line. You just got a list of things, paragraphs or objects maybe, that had changed and those that hadn’t. Most of the time, that’s enough. With databases, you have a list of all the objects you want to track in either the source or target of the comparison, or in both. You also have a pair of characters that denote whether they are …
| -> | Not in the source but in the target |
| <- | In the source but not in the target |
| <> | In both but different |
| == | In both and the same. |
SQL Compare can usually detect name changes, so it lists the names of both objects that are compared.
The overview of schema differences
We generally want to compare tables, views, functions in two databases. To do this, we can bring up the SQL Compare GUI in its full majesty, but we really just want the helicopter view, not the full drill-down. Actually, I have no idea what you want, because of the wide range of practices in database development. This calls for a configurable system that is easily automated but allows you to take in the overview of the differences. The joy of a script is that you can easily change it to work the way you require. You may want it to receive this information in a message, a web page in a spreadsheet, or maybe just plain text.
Here is the sort of output I like, which I have displayed in a spreadsheet.
If I then want to drill into the detail, and I have the time, I then use the SQL Compare GUI.
Producing the Diff
SQL Compare command-line produces a report. Although it is in XML, it is perfectly readable via PowerShell. It goes into more detail than we want at this stage, but we can extract from it the DIFF overview.
When using SQL Compare command-line, the easiest way, if it is suitable, is to use the project file as a parameter. You can generate this file by creating and improving a project, with all the options, The INCLUDE objects (list of objects you want it the report) and the EXCLUDEs (them you don’t). I’ve explained how to automate comparison with project files elsewhere: Automating Schema Comparisons with SQL Compare Projects and PowerShell.
I do this a lot when I’m working intensively on a particular database, but it isn’t quite flexible enough for our purposes and doesn’t provide all the information we need. Instead, we’ll pass all our projects options to SQL Compare command line using an XML Arg file. This is a good second best, and quite a lot of it can be generated from a favorite project file that does comparisons the way you want. Once you start being more specific about how you want comparisons to run, the less realistic it is to use the raw command-line parameters. The obvious problem you have with XML Arg files is that any passwords are stored in plain text. I’ve already shown how to deal with that in another article.
This script that I introduce here will look complicated, but it will pick up passwords that are stored securely in a protected area of storage to create an XML file that is then deleted. The script begins with the definition of a data object. This is done so you can save all the details in a file, either JSON or XML, which keeps everything together. If you have a number of databases to check, you can put all the files in a directory, and just iterate through them, executing the script with that data object.
The diffing script
We start with the object that holds all our data. A lot of this information can be taken from a project file. I prefer to refer to source and target because the left to right, and direction analogy is irrelevant. The $requirement data object can be saved as a JSON or XML file and used instead of having all this information in the script.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
<# We start off by putting our requirements into a hashtable, but this can be read in as a json object when you get to automate the process entirely. No passwords are ever kept in the hashtable or jspn file #> $Requirement = @{ #we put our requirement into a hashtable 'ProjectName' = 'PubsDiff'; 'Databases' = #the details of the source and target database @{ 'Source' = @{ # the source is compared to the target 'Server' = 'MyServer'; # the name of the source server 'DatabaseName' = 'pubs'; # the name of the source database 'Uid' = 'PhilFactor'; # leave blank if you are using domain authentication }; 'Target' = @{ #the target would be changed to the source 'Server' = 'MyServer'; #if synch script executed 'DatabaseName' = 'pubsone'; # the name of the target database 'Uid' = 'PhilFactor'; <# the userid for this database leave blank or null if you are using domain authentication #> } } 'options' = @('IgnorePermissions', 'DecryptPost2kEncryptedObjects', 'IgnoreCollations', 'IgnoreDatabaseAndServerName', 'IgnoreFileGroups', 'IgnoreFillFactor', 'IgnoreSquareBrackets', 'IgnoreSystemNamedConstraintNames', 'IgnoreTSQLT', 'IgnoreUserProperties', 'IgnoreWhiteSpace', 'IgnoreWithElementOrder' <# See for the details about options https://www.red-gate.com/hub/product-learning/sql-compare/exploring-the-sql-compare-options #> );<#If you set any options explicitly, all the default options are switched off.#> #'include' = @('Table','View','function'); #this is an array of opbects to include 'ReportType' = 'XML'; # no json type at the point where I write this 'ReportLocation' = "$env:TMP\SCReport.xml"; 'LogLevel' = 'Warning'; 'Exclude' = @('table:flyway_schema_history'); #this is an array of objects to exclude 'Force' = $true # you over-ride the report if it exists. } |
We will need a helper function to retrieve any password.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
function SavedPassword ($SqlUserName, $server) { $SqlEncryptedPasswordFile = ` "$env:USERPROFILE\$($SqlUserName)-$($server).xml" # test to see if we know about the password in a secure string stored in the user area if (Test-Path -path $SqlEncryptedPasswordFile -PathType leaf) { #has already got this set for this login so fetch it $SqlCredentials = Import-CliXml $SqlEncryptedPasswordFile } else #then we have to ask the user for it (once only) { #hasn't got this set for this login $SqlCredentials = get-credential -Credential $SqlUserName $SqlCredentials | Export-CliXml -Path $SqlEncryptedPasswordFile } $SqlCredentials.GetNetworkCredential().password } |
The rest of the script just creates the XML Arg file (Paramfile.xml) from our data object, and passes it to SQL Compare command line, which executes it. It then fetches SQL Compare’s XML report file and produces the DIFF from it. It is configured to produce the DIFF as a CSV file, and a table in the PowerShell output, but because it is a PowerShell object, it can be converted to just about any common format.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 |
#please define the location of SQL Compare $SQLCompareAlias = "${env:ProgramFiles(x86)}\Red Gate\SQL Compare 14\sqlcompare.exe" <#if you specify an XML Argfile on the commandline, you can't then specify any other switches on the command line except /verbose or /quiet multiple options should be separated with commas:#> $Source = $Requirement.Databases.Source $Target = $requirement.Databases.Target $SCcommandline = [xml] @" <?xml version="1.0"?> <commandline> <!--report on any differences between two databases --> <loglevel>$($requirement.LogLevel)</loglevel> $(if ($Requirement.Options -ne $null) { '<options>' + ($Requirement.options -join ',') + '</options>' } else { '' }) $(if ($Requirement.Include -ne $null) { '<include>' + ($Requirement.include -join "</include>`n<include>") + '</include>' } else { '' }) $(if ($Requirement.Exclude -ne $null) { '<exclude>' + ($Requirement.exclude -join "</exclude>`n<exclude>") + '</exclude>' } else { '' }) $(if ($Requirement.Force -eq $true) { '<force />' } else { '' }) <Server1>$($Source.Server)</Server1> $(if ($Source.Uid.length -gt 0) { ' <userName1>'+$($Source.Uid)+'</userName1> <password1>' + (SavedPassword $Source.Server $Source.Uid) + '</password1>' } else { '' }) <Database1>$($Source.DatabaseName)</Database1> <Server2>$($Target.Server)</Server2> <Database2>$($Target.DatabaseName)</Database2> $(if ($Target.Uid.length -gt 0) { ' <userName2>'+$($Target.Uid)+'</userName2> <password2>' + (SavedPassword $Target.Server $Target.Uid) + '</password2>' } else { '' }) <report>$($requirement.ReportLocation)</report> <reportType>$($requirement.ReportType)</reportType> </commandline> "@ Set-Alias SQLCompare $SQLCompareAlias -Scope Script if (!(test-path ((Get-alias -Name SQLCompare).definition) -PathType Leaf)) { Write-error 'The alias for SQLCompare is not set correctly yet' } #the database scripts path would be up to you to define, of course $SCcommandline.Save("$env:temp\Paramfile.xml") sqlCompare /Argfile:"$env:temp\Paramfile.xml" >"${env:temp}\log.txt" Remove-Item -Path "$env:temp\Paramfile.xml" [xml]$XMLReport = Get-Content -Path $requirement.ReportLocation -raw $VerbosePreference = 'silentlycontinue' #$XMLReport.innerxml $Report = @(); $Report += $XMLReport.comparison.differences | foreach{ $_.difference } | foreach{ $objectType = $_.fqn -split '-' @{ #'fqn'=$_.fqn; 'Comp' = if ($_.status -ieq 'onlyin2') { '->' } elseif ($_.status -ieq 'onlyin1') { '<-' } elseif ($_.status -ieq 'same') { '==' } else { '<>' }; 'Type' = $objectType[0]; 'Source' = if ($_.status -ne 'onlyin1') { $objectType[1] } else { '' }; 'Target' = if ($_.status -Ne 'onlyin2') { $objectType[1] } else { '' } } }; #show the result $Report | foreach{ [PSCustomObject]$_ } | Format-Table -AutoSize #Write it out as a CSV for display in excel etc. $report | foreach{ [PSCustomObject]$_ } | convertTo-csv > "$($env:HOMEDRIVE)$env:HOMePath\$($Requirement.ProjectName).csv" |
In my case, with the databases I’ve chosen, this produces the following Diff.
Generating Diffs for other database development tasks
Once you have this Diff checker, you can use it for all the many tasks that SQL Compare can perform as part of development work. This means using other sources than live databases. You can generate a diff report by comparing a live database with a script folder, build script or snapshot, for example.
You might want to track the progress of development by taking snapshots of each significant version so you can subsequently see immediately what objects have changed. You could do the same thing with scripts directories, even when they are in source control. These will need different data such as the path to the directory. There are many possible combinations, so I’ll have to leave it to the interested readers to develop the scripts to their liking
In fact, any of these types of representation of a database can be converted to any other. This makes this type of Diffing script very useful for finding out when objects changed and what has changed in any particular release.
Conclusion
For a lot of development work, it makes sense to maintain a record of what has changed and when if this can happen painlessly without routine effort. This information is available, of course, in source control if you have the time to find it, but with SQL Compare and this type of script, you have the potential for a lot more immediate information to help to disentangle merges of features or finding the most likely suspects for bugs. It doesn’t tell you what’s wrong but it tells you where to look for the detail.
Tools in this post
You may also like
-
Article
Automating Flyway Undos
How to auto-generate first-cut undo scripts for every Flyway migration. For every new version of a database created by a Flyway versioned migration, we compare it to a 'source' directory containing object-level build scripts for the previous version. The SQL Compare engine does the rest, producing the associated undo script that will revert the database to the previous version, if required.
-
Article
You just Build It don't you? 12 Common Database Build Blockers
Database deployments, like the sheep of exasperated hill-farmers, often find strange and unexpected ways to self-destruct. Phil Factor describes the most common things that can go wrong, and how a reliable automated database build process can prevent messy accidents.
-
Article
Database DevOps for Pragmatists: A Datafaction Case Study
Ami Adler, a Software Development Manager at Datafaction explains how they used Redgate tools, and others, to introduce automation and testing into their Database DevOps processes and so achieved a faster and more reliable deployment process for their application.
-
Article
How to build multiple database versions from the same source: pre-deploy migration scripts
The final part of Alex Yates's three-part series tackles the complicated issue of automating deployments when the same table might have a different structure, in different production versions of the database.
-
