Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
wbb1975
Starting Member
23 Posts |
 Posted - 2006-10-04 : 06:21:33
|
| ... does anybody of you have a script for validating all stored procedures within a sql-server database (2000) ...thanks in advanceGreetingsStefan |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-10-04 : 06:25:58
|
| Can you clarify "validating" in this context please?Kristen |
 |
|
|
wbb1975
Starting Member
23 Posts |
Posted - 2006-10-04 : 06:42:28
|
| 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 ... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-10-04 : 06:56:26
|
| Tricky.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!Kristen |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-10-04 : 06:59:47
|
| "we have made several changes to our database"I should have also said: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"Kristen |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-10-04 : 07:13:46
|
| I don't this forum is made for requesting scripts.Peter LarssonHelsingborg, Sweden |
|
|
|
|
|
|
Validating all Stored procedures