How can you mass-validate stored procedures, views, etc?

I am looking for a way to validate all objects in the database that is is
possible for in some automated fashion. For instance, if a column is droppe
d
from a table stored procedures that rely on it will fail on the next
execution, but I want to be able to run a process that will reveal that prio
r
to execution. I would want to do that for views and user defined functions
as well. Other than executing every procedure and function and selecting
from every view does anyone know of a way to do this?
I thought I'd just script the objects out for ALTER and run that script
against the database but it appears you can only create an ALTER script one
object at a time through EM and with thousands of objects I really don't wan
t
to go through that.
I created a script that ran sp_recompile against every stored procedure,
then another block that executed every stored procedure without supplying an
y
parameters and that seemed to catch stored procedures that relied on missing
columns. The problem with that is it makes the assumption that the stored
procedure would either fail when no parameters were supplied, or would be
harmless when the stored procedure executed either because it required no
parameters or had defaults for all parameters. Of course this also only
worked for stored procedures and not the functions and views.
I made the mistake of changing a view without realizing that it was used in
a stored procedure and when that stored procedure executed it failed. You
can't depend on the dependencies that SQL maintains since they are not alway
s
correct, so you can't even make note of the dependent objects and selectivel
y
check only those objects without risking missing a dependency.
Any suggestions would be greatly appreciated.Byron (Byron@.discussions.microsoft.com) writes:
> I am looking for a way to validate all objects in the database that is
> is possible for in some automated fashion. For instance, if a column is
> dropped from a table stored procedures that rely on it will fail on the
> next execution, but I want to be able to run a process that will reveal
> that prior to execution. I would want to do that for views and user
> defined functions as well. Other than executing every procedure and
> function and selecting from every view does anyone know of a way to do
> this?
> I thought I'd just script the objects out for ALTER and run that script
> against the database but it appears you can only create an ALTER script
> one object at a time through EM and with thousands of objects I really
> don't want to go through that.
>...
This is indeed a difficult situation, and I don't really think there
is a single good way around it.
The method in the above paragraph is fairly easy to apply: use Generate
Scripts from Enterprise Manager to script the entire database. Then
open the generated script in a an editor and change CREATE PROCEDURE
to ALTER PROCEDURE.
However, you may not catch all errors this way. Say that you have:
INSERT #temp (...)
SELECT ..., missing_column
FROM ...
Because of deferred name resolution, SQL Server will not alert you of the
missing column. It sees that #temp is missing, and will defer compilation
of that statement until #temp has been created, and thus the error will
not occur until run-time.
The approach I use myself is based on the fact that we have all code
under version control. We also have a build tool that goes head over
heels to nullify the effect of deferred name resolution, by creating
all temp tables in an SP before loading the procedure. Thus a missing
column is noticed directly. Furthermore, it's easy for me to build an
empty database from SourceSafe, which will give me a complete sysdepends
that I can rely on.

> I created a script that ran sp_recompile against every stored procedure,
> then another block that executed every stored procedure without
> supplying any parameters and that seemed to catch stored procedures that
> relied on missing columns. The problem with that is it makes the
> assumption that the stored procedure would either fail when no
> parameters were supplied, or would be harmless when the stored procedure
> executed either because it required no parameters or had defaults for
> all parameters. Of course this also only worked for stored procedures
> and not the functions and views.
Our build-and-load tool is available for free download on
http://www.abaris.se/abaperls/. To get started with it, may be overmuch.
However, the toolset in includes a free-standing tool SPTRITEST which
performs essentially the above, but also provides dummy value for all
parameters, so it permits you test far more procedures. It goes without
saying that you should run this on a *copy* of the database, or else
you get a load of junk in it.
And it may still not find all cases of missing columns, since if there
are IF-ELSE branches, you are likely to pass over only one of them.
I also need to add the disclaimer that I wrote it in 6.5 days, and I
have not used it myself for ages.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx