Fog Creek Software
Discussion Board




Comparing MS SQL Server SP and DB structure

The company I work for develops an application which uses lots of MS SQL Stored procedures. Currently we have a few clients with few instances of the database and the application. When performing upgrades or troubleshooting it would be very nice to have a way to quickly compare the database structure and stored procedure code between the two similar, but slightly different databases (production and development, or production and staging, for instance).
What I do is to generate SQL scripts for both databases and compare them with WinDiff. I have two options:

1. Generate one file for the entire database. Then it is very hard to observe differences because of the large size of the file.

2. Generate separate files for each stored procedure and table. However, for some reason the stored procedures sometimes start with blank lines (I assume this is consequence of how I update them). In this case WinDiff marks the files as different. However when I double-click at the files it says 'different in blanks only' and marks them as same.

Is there a way to make WinDiff show the files which differ in blanks only as same from the very beginning? Is there another, more effective way to compare scripts? I am not looking for version control solution, for lots of reasons I want to be able to see and compare the current actual contents of the database scipts and tables between the development Db and production DBs.

Alexander Chalucov
Thursday, April 17, 2003

The following merge tool kicks butt.  We use it all the time, both within and outside source control:

http://www.araxis.com/

It has options regarding the handling of blanks.

Bill Carlson
Thursday, April 17, 2003

Sql Server Compare is a free toll that I have found to be very useful in this situation.  You can find it at webattack

http://www.webattack.com/get/sqlcompare.shtml

K
Thursday, April 17, 2003

Another option is to roll your own db scripting tool.  I wanted to have a scripting tool that I could run from the scheduler, use simple matching expressions to control what got scripted, other little niceties not present in EM.  SQLDMO makes writing something like this not very hard.  If you don't like the SQLCompare utility, I'd take a look at SQLDMO and see what it does.  It's pretty cool.

Matt Conrad
Thursday, April 17, 2003

If you can get a copy of the standard unix diff utility (e.g. through cygwin or a port) , try that. The man page I have here says the -B option will ignore changes that are just insertions/deletions of blank lines, and -b will ignore whitespace changes.

Unfortunately, you lose your gui then. But if you are installing cygwin anyway, you could just script it and generate reports.

I've not used WinDiff, so I don't know how much of a step backwards this would be. But it's another thing you can try, at least. ALternatives are good.

Mike Swieton
Thursday, April 17, 2003

Thank you all for your advice. The SQL server compare tool seems to work perfectly for me and it is the solution I will stick to with the time being. I will also consider using scripts to do the comparison and some automated tasks for that, but these are solutions which will not pay off in the short term. Maybe when we have more customers on the system they would make more sense. Thank you all again.

Alexander Chalucov
Thursday, April 17, 2003

Wow, I wasn't looking for a SQL server compare tool but now that I've found one I don't know why the heck I wasn't.  That SQL Server Compare tool is the bomb!

The only thing I wish it could do is compare multiple databases on a server all in one shot.  But for free, I'm not complaining.  It's still a huge timesaver.

- Rick

Rick @ CA
Thursday, April 17, 2003

I use a product called SQLCompare by Red Gate Software:
http://www.red-gate.co.uk/sql/summary.htm

I am also demo-ing another app called SQLDiff by Lockwood Tech:
http://www.lockwoodtech.com/frame_products.htm

Cade Bryant
Friday, April 18, 2003

Embarcadero Change Manager: http://www.embarcadero.com/products/changemanager/index.asp

J
Tuesday, April 22, 2003

*  Recent Topics

*  Fog Creek Home