I’ve grown up reading Tom Clancy and probably most of you have at least seen Red October, so this book caught my eye when browsing used books for a recent trip. It’s a fairly human look at what’s involved in sailing on a Trident missile submarine…
Tablediff is a command line utility that comes with SQL Server to compare the data and schema in two tables for differences and generate a T-SQL script to synchronize them. It’s a rudimentary but useful tool that most people don’t know is available and it gets the job done when you don’t have a more sophisticated tool like Red Gate’s SQL Compare at your disposal.
You can find the tool in a version-specific subfolder of your SQL Server shared features installation directory. For example, look in C:\Program Files\Microsoft SQL Server\110\COM if you’ve got SQL 2012 installed on the C: drive.
I’ve always assumed that it’s included with Management Studio but recently I found out that’s not the case. After testing various installation options I discovered that it’s included when the SQL Server Replication features are installed, meaning you also need to install the Database Engine Services to get it:
Although not limited to replication scenarios, Microsoft’s documentation hints that it was designed with replication in mind:
The tablediff utility is used to compare the data in two tables for non-convergence, and is particularly useful for troubleshooting non-convergence in a replication topology.
Perhaps this is the reason most people don’t know about it – replication isn’t widely used and if you exclude the feature from your database install you won’t get tablediff.
Now you know…