Invalid stored procedures [How to check?]

I am on a development project where we are using SQL Server 2000 and using
almost exclusively stored procedures.
Because the team is all over the country we have run into a problem.
Because we are in the development/design phase things are changing as new
requirements are thrust upon us by managment.
Sometimes the datatype of table changes or a column is deleted. This makes
the stored procedure invalid. However, you don't know its invalid until you
access the stored procedure.
Is there a way to know that a stored procedure has become invalid or is
there a way to force a recompile so that you can see which stored procedure
now have problems?
Thanks!
EnzoPrior to posting my original message I read the portion of the BOL that you
pasted to this message.
The problem with sp_recompile is that it does not actually do the recompile!
It only marks it to be recompiled. What I want is way to actually
recompile all my stored procs without essentially dropping and recreating
them to find which ones are now invalid.
In Oracle and other DBMS system a recompile is an actuall recompile and not
simply marking a flag somewhere in the DD.
I guess the only way to do this is how we are doing it now which is to drop
and recreate every single stored proc.
"ilovesql" <ilovesql@.hotmail.com> wrote in message
news:ufC3E#kRDHA.2460@.TK2MSFTNGP10.phx.gbl...
> BOL:
> Recompiling a Stored Procedure
> As a database is changed by such actions as adding indexes or changing
data
> in indexed columns, the original query plans used to access its tables
> should be optimized again by recompiling them. This optimization happens
> automatically the first time a stored procedure is run after Microsoft®
SQL
> ServerT 2000 is restarted. It also occurs if an underlying table used by
the
> stored procedure changes. But if a new index is added from which the
stored
> procedure might benefit, optimization does not automatically happen (until
> the next time the stored procedure is run after SQL Server is restarted).
> SQL Server provides three ways to recompile a stored procedure:
> a.. The sp_recompile system stored procedure forces a recompile of a
> stored procedure the next time it is run.
>
> b.. Creating a stored procedure that specifies the WITH RECOMPILE option
> in its definition indicates that SQL Server does not cache a plan for this
> stored procedure; the stored procedure is recompiled each time it is
> executed. Use the WITH RECOMPILE option when stored procedures take
> parameters whose values differ widely between executions of the stored
> procedure, resulting in different execution plans to be created each time.
> Use of this option is uncommon, and causes the stored procedure to execute
> more slowly because the stored procedure must be recompiled each time it
is
> executed.
>
> c.. You can force the stored procedure to be recompiled by specifying
the
> WITH RECOMPILE option when you execute the stored procedure. Use this
option
> only if the parameter you are supplying is atypical or if the data has
> significantly changed since the stored procedure was created.
>
> Note If an object referenced by a stored procedure is deleted or renamed,
> an error is returned when the stored procedure is executed. If, however,
an
> object referenced in a stored procedure is replaced with an object of the
> same name, the stored procedure executes without having to be recompiled.
>
> To recompile a stored procedure next time it is run
> Transact-SQL
>
> See Also
> Creating a Stored Procedure
> Deferred Name Resolution and Compilation
> Executing a Stored Procedure
> Programming Stored Procedures
> "Enzo" <touringcarclub@.hotmail.com> wrote in message
> news:e$JHDbjRDHA.2148@.TK2MSFTNGP11.phx.gbl...
> > I am on a development project where we are using SQL Server 2000 and
using
> > almost exclusively stored procedures.
> >
> > Because the team is all over the country we have run into a problem.
> >
> > Because we are in the development/design phase things are changing as
new
> > requirements are thrust upon us by managment.
> >
> > Sometimes the datatype of table changes or a column is deleted. This
> makes
> > the stored procedure invalid. However, you don't know its invalid until
> you
> > access the stored procedure.
> >
> > Is there a way to know that a stored procedure has become invalid or is
> > there a way to force a recompile so that you can see which stored
> procedure
> > now have problems?
> >
> > Thanks!
> > Enzo
> >
> >
>
>