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).
The following merge tool kicks butt. We use it all the time, both within and outside source control:
Sql Server Compare is a free toll that I have found to be very useful in this situation. You can find it at webattack
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.
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.
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.
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!
Rick @ CA
I use a product called SQLCompare by Red Gate Software:
Embarcadero Change Manager: http://www.embarcadero.com/products/changemanager/index.asp
Fog Creek Home