How to easily compare 2 SQL Server Databases.

NOTE: this is for SQL SERVER databases only.  It will not sync Oracle to Interbase or MySql to Sybase.  However, application of this technique may apply to any two databases of the same type. (i.e. oracle to oracle)

I had lost access to the  production database for a time, and wanted to ensure that I had propagated all my recent changes from Development to production - without spending $895 for a SQL management and database analysis system.

So here's how I proceeded to sync my tables from one database to the other.

Open the Production database (or a current backup of it) in SQL Server Management studio.
use this query:

SELECT
    TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE, DATETIME_PRECISION
FROM INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA='dbo'
order by 1,2,4

Note: these columns were important to me, feel free to modify the columns as you see fit.  Also I only cared about the 'dbo' schema, so I filtered for that.  Your needs may vary.

This will give you the column info for all tables in the 'dbo' schema.

Now, save the data by right clicking the grid and selecting Save Results As.

Give it a name like Production_Schema and save as .txt.

Note: csv works too, but I find txt easier to compare.

Next do the same with the development database, naming it something like Development_Schema.txt.

Now you need a diff tool like WinMerge.  Compare these 2 files to see where they differ.  The text files cover all columns in all datatables and views in the schema.

Now go through the differences and see what changes have to be made in production so your software doesn't crash.  When I did this, I noted that there were some changes that were not ready for prime-time yet, so I left them unfixed in production.

The only thing left to compare once this is complete are the stored procedures and functions.  I just made a fresh, empty query and did a Script Stored Procedure As > Drop and Create to > Clipboard and pasted every one of them into the query. I saved it as AllFunctions.SQL and ran it in production to sync the functions.

 I hope this helps!

Comments are welcome!
...

---

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.