In one of my current projects I have to import an Oracle database dump into a development database every two weeks. But more than once there were tables missing, columns have been changed, or indices have been removed in the new dump. Of course, that led to problems with the programs that used the database.
To be able to find the differences between the old and the new database I wrote a small C# program that “diffs” two databases. It generates a textfile containing
- a list of all tables
- including the number of rows per table
- a list of all indexes
- a list of all columns and their configuration
Furthermore, it compares two of these text files and produces an output file like this:
== Missing tables == TABLE1 == Added tables == TABLE2 = Less rows = TABLE3: 123 -> 100 = Equal number of rows = TABLE4: 17 -> 17 = More rows = TABLE5: 123 -> 234 == Missing indexes == TABLE3.COL1 == Added indexes == TABLE4.COL2 == Missing Columns == TABLE3.COL4 == Added Columns == TABLE3.COL5
So, before I import the new dump, I create a textfile with DBDiff that contains the current database state. After the import I create another one and compare it to the old file giving me all the changes to the database structure. Of course, the tool could also be used to compare a production database to a development database.
Here are two screenshot showing the tool in action:
You can download the program including its source code here:
All needed configuration (DSN, credentials etc.) is done in DBDiff.exe.config. The database needs to be configured and reachable as an ODBC source.