Check if Stored procedures have syntax errors

Question

I have tons of stored procedures in my database. We are constantly changing the data structure (we are in development) Is there a tool that will tell me which stored procedures won't compile?

When you create a stored procedure it prevents you if there is an invalid table or column, but if you change the column name after the stored procedure is created, your proc is invalid. I'd like to get a list of those invalid procs.

cheers!

Joseph

Answer 1

Your best bet is to write some database unit tests, if you are using VS2008 then it is pretty simple to do, but time-consuming.

But, then you can run your tests and ensure that everything is either working as you expect, or get a list of functions you need to look at.

You may find this article on database unit testing to be interesting, but I do believe it may require VS Team System.:

http://www.developer.com/db/article.php/3758601/Introducing-Visual-Studio-Team-System-2008-Database-Unit-Testing.htm

Answer 2

You can use this tool put together at CodeProject which uses built-in SQL functions and options to generate a text file of invalid stored procedures.

http://www.codeproject.com/KB/database/validatingsql.aspx