OK ... we have made several changes to our database so some stored procs are not valid anymore (referencing old columns) ... But I need to have ONLY valid stored procs within the database ...
You could script all the Sprocs and DROP and re-CREATE them. That would give you syntax errors for columns that were unreferenced, but it won't do that in all circumstances.
We used automated regression testing to catch these sorts of issues. Basically the automated equivalent of having someone go through every scenario in the application that we can think of!
We have Column Rename and Table Rename "Standard Operating Procedures" here. Those including searching for occurrences of the old name in the source code and the stored procedures, and then any location in the application that calls those stored procedures, and so on - to try to catch the issues at the earliest opportunity!
To make this easier we use unique column names in our application - e.g. we do NOT have "ID" in each table, we have names more like "Customer_ID" and "Supplier_ID" - such that "Customer_ID" is unique within the system, and column joining to it (which might also need changing) are then called "Order_Customer_ID"
Validating all Stored procedures
Posted - 10/04/2006 : 06:21:33
