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.
-
Possible answer here (worked for me): stackoverflow.com/questions/3027399/…– Manuel DíazCommented Apr 1, 2019 at 20:57
Add a comment
|
4 Answers
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):
answered Jan 10, 2013 at 13:44
-
Thanks for the hints/links... I'll see if any of them can be used in my setup. Commented Jan 11, 2013 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. Commented Jan 11, 2013 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. Commented Jan 11, 2013 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. Commented Jan 14, 2013 at 7:27
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.
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.
-
For one, Red Gate provides SQL development tools to help you manage this (versioning, comparison, unit testing, etc).– pritaeasCommented Jan 11, 2013 at 14:46
Ok, newbie answerer here. Using sp_refreshsqlmodule you can check individual validity inside a loop. A user of stack overflow posted a script which does exactly that: loops through sys.objects and tries to refresh the module. I've tested it and it works perfectly. Link here: https://stackoverflow.com/questions/3027399/how-to-check-all-stored-procedure-is-ok-in-sql-server
