Product articles
SQL Compare
Schema Comparison Options
A Quick Diff Checker for SQL Server…

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.

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

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.

A diff report for two SQL Server databases

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.

We will need a helper function to retrieve any 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.

In my case, with the databases I’ve chosen, this produces the following Diff.

The final diff report

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

A diff report for other schema comparison tasks

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

SQL Compare

Compare and synchronize SQL Server database schemas

Find out more