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.

 All Forums
 General SQL Server Forums
 Script Library
 Validating all Stored procedures

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 advance

Greetings
Stefan

Kristen
Test

22859 Posts

Posted - 2006-10-04 : 06:25:58
Can you clarify "validating" in this context please?

Kristen
Go to Top of Page

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 ...
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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 Larsson
Helsingborg, Sweden
Go to Top of Page
   

- Advertisement -