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.
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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):
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.