SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Validating all Stored procedures
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

wbb1975
Starting Member

23 Posts

Posted - 10/04/2006 :  06:21:33  Show Profile  Reply with Quote
... 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

United Kingdom
22403 Posts

Posted - 10/04/2006 :  06:25:58  Show Profile  Reply with Quote
Can you clarify "validating" in this context please?

Kristen
Go to Top of Page

wbb1975
Starting Member

23 Posts

Posted - 10/04/2006 :  06:42:28  Show Profile  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 10/04/2006 :  06:56:26  Show Profile  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 10/04/2006 :  06:59:47  Show Profile  Reply with Quote
"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

Sweden
30114 Posts

Posted - 10/04/2006 :  07:13:46  Show Profile  Visit SwePeso's Homepage  Reply with Quote
I don't this forum is made for requesting scripts.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000