Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite
1

How can I verify all stored procedures in a SQL Server database? I need to verify if all stored procedures still work, after some tables/views etc. have been deleted or changed.

share|improve this question

3 Answers

up vote 4 down vote

No, there is no built-in way to do this within SQL Server. As Marian stated, you could recompile all of your procedures to be sure they're still valid, but this doesn't prove they'll still work (and don't forget that deferred name resolution makes this validation less than stellar anyway). However you can use other tools such as SQL Server Data Tools (SSDT) to help facilitate unit testing.

Just like changes to your application, if you change your schema, you need to test those changes. How automated you make your unit testing depends on the complexity of your schema, the consuming application(s), and the number of permutations possible for stored procedure outcomes (including both explicit input of different parameters or different parameter values, and implicit input such as time of day, state of the system, specific data at rest, etc.). You have to keep in mind that in some cases you will expect the stored procedure output to stay the same, but in others you will actually expect it to be different.

Some potentially helpful links (a couple culled from a question on SO geared to testing a single stored procedure):

share|improve this answer
Thanks for the hints/links... I'll see if any of them can be used in my setup. – Kim Rasmussen Jan 11 at 14:32
We tried to script all stored procedures and run them again in the Query window - and found a few errors (= someone have made changes to a table and not verified all the stored procedures). But the best way is probably to verify the procedures during the build process. I think it can be accomplished if we create a DB solution (in Visual Studio) with all the table structures and creates a new (empty) database every time and run the procedures agains this database. – Kim Rasmussen Jan 11 at 15:03
You'll need some way to keep track of all the permutations you need to test, too. For example stored procedures (especially those with optional parameters) may behave differently depending on the parameter values. I don't think just running each stored procedure once is a very valid test. – Aaron Bertrand Jan 11 at 15:15
You are absolute right... Its not a full test and it depends on how your stored procedures are created. But it was a start - until we find a better an more reliable way. – Kim Rasmussen Jan 14 at 7:27
up vote 3 down vote

I'm not sure there's any other way to verify all of them than by really executing them with proper parameters. Even if you try to compile all procedures again you can be using dynamic sql inside the procedures and miss runtime errors.

I'd say that you need to have a good procedures' trace before and use it after to simulate a good load.

Another idea is to really have a comprehensive suite of tests for all procedures and when doing any schema changes you execute the test suite.

share|improve this answer
up vote 2 down vote

Visual Studio can validate the existence of objects named in your stored procedures, if you are using VS to manage your database projects. There may be other third-party tools that can do something similar, but I've not been introduced to any.

share|improve this answer
For one, Red Gate provides SQL development tools to help you manage this (versioning, comparison, unit testing, etc). – pritaeas Jan 11 at 14:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.