Working on a team where people are prone to amending dev SQL Server tables and forgetting about it, or preparing a change for deployment and having to wait for that deployment. This leaves our dev and live tables inconsistent, causing problems when SPROCs are pushed live.
Is there a tool whereby I can enter a SPROC name and have it check all tables referenced in it in the dev and live DBs, and notify of any differences?
Try Microsoft Visual Studio Database Edition aka Data Dude (formerly for Database Professionals). It'll do a complete schema comparison and generate the necessary scripts to upgrade the target schema.
Of course, this shouldn't replace a proper build process ;-)
I know two excellent tools for diffing SQL database structures - they don't specifically look inside stored procedures at their text, but they'll show you structural differences in your databases:
Redgate also has a SQL Dependency Tracker which visualizes object dependencies and could be quite useful here.
Marc
I would recommend SQL compare and SQL Data Compare from Redgate Software. I worked with these tools for several projects and they did a great job. Documenting changes is also a good thing to do, but some changes are to complex to write your own SQL code for (including juggling data around between tables).
The redgate tools create scripts in a matter of seconds and those scripts are almost always correct (some older versions had a hard time with table dependencies in big databases, but when playing around with the statements (in a begin transaction / rollback) I was able to quickly fix those problems).
Another strong point in the redgate suites is that you can save your comparison project. This is especially useful when you don't want to convert a certain table (or data), you can exclude them. When loading the project the next time the software will automatically ignore those tables.
One disadvantage is the cost of the software (smaller companies I worked with did not want to buy the software). SQL compare and SQL data compare together will cost you about 800 dollars, but if you look at the time you will save when releasing you will save a lot of money. There is also a trial you can play around with (30 days I believe).
If you need a quick schema comparison tool for SQL Server, you should take a look at dbForge Schema Compare for SQL Server.
