Parse all stored procedures in a database

Question

Does anyone know of a way to verify the correctness of the queries in all stored procedures in a database? I'm thinking of the scenario where if you modify something in a code file, simply doing a rebuild would show you compilation errors that point you to places where you need to fix things. In a database scenario, say if you modify a table and remove a column which is used in a stored procedure you won't know anything about this problem until the first time that procedure would run.

Answer 1

What you describe is what unit testing is for. Stored procedures and functions often require parameters to be set, and if the stored procedure or function encapsulates dynamic SQL--there's a chance that a [corner] case is missed.

Also, all you mention is checking for basic errors--nothing about validating the data returned. For example - I can change the precision on a numeric column...

This also gets into the basic testing that should occur for the immediate issue, and regression testing to ensure there aren't unforeseen issues.

Answer 2

You could create all of your objects with SCHEMABINDING, which would prevent you from changing any underlying tables without dropping and recreating the views and procedures built on top of them.

Depending on your development process, this could be pretty cumbersome. I offer it as a solution though, because if you want to ensure the correctness of all procedures in the db, this would do it.