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.


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):

  • Thanks for the hints/links... I'll see if any of them can be used in my setup. Jan 11 '13 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. Jan 11 '13 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. Jan 11 '13 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. Jan 14 '13 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).
    – pritaeas
    Jan 11 '13 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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