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.