SQL Server 2005 Stored Proc validity check

[aisling]

Hi All

I have spent the last couple of days re-writing stored procedures for one of our systems. These sprocs are rather complicated with branches in the code depending on both params and the results of selects within the sproc. Now SSMS does some syntactical validation on the code but as we all know SQL Server does not do referencial checks on the tsql statements(insert, update,delete...) at creation time.

The only way you find out if the statement in the stored proc has a typeo or references a column or table that does not exist is if you execute the procedure and the execution plan includes the statement in question. With the size of - and number of statements in these stored procedures this is a problem for me. Does anyone know how I can validate all the statements in a stored procedure without having to run the stored procedure with parameters and data that will test every conceveble execution plan? All I need is for SQL server to check if the tables I reference in the sproc and their related columns exist and tell me if they dont'

any help and suggestions would be great

 

[stevenv]

No easy way that I know of, maybe some expensive third party software can do it. The problem is that SQL Server will allow you to create stored procedures referencing objects that do not even exist yet: they can be created after the stored procedure is. If you do reference a non-existent object, it will just report an invalid object name at runtime (as you've probably seen). There are pros and cons to this approach: makes object scripting simpler, eliminating complex dependencies but obviously makes validation or dependency checking a bitch, if not impossible.

 

[aisling]

Thanks stevenv, After quite a bit of searching I came to more or less the same conclusion. The only way I can see to get around this is to strip out all the conditional statements and then run the sp, that however I think is easier said than done.

 

[aisling]

Does anyone know of a 3rd party tool that would do this?