How to Force all Stored Procedures to "recompile"?

Is there a way to force all stored procedures to "recompile" or create a new
execution plan? What we are trying to do is find a way to quickly identify
all stored procedures that are invalid because of schema changes like table
s or columns dropped or alt
ered.
Thanks,
BLGYou are asking two different things. AFAIK, there's no way to know which pla
ns are invalidated (a plan can be
invalidated for several reasons).
If you want a proc to recompile at next execution, you can use sp_recompile
on any of the tables that the proc
is using.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"blg" <anonymous@.discussions.microsoft.com> wrote in message
news:1C9AE715-5303-4FC6-ABD2-6ADCA7B6FF87@.microsoft.com...
> Is there a way to force all stored procedures to "recompile" or create a new execu
tion plan? What we are
trying to do is find a way to quickly identify all stored procedures that ar
e invalid because of schema
changes like tables or columns dropped or altered.
> Thanks,
> BLG|||Hi,
Run the command DBCC FREEPROCCACHE to remove all compile plans from the
procedure cache.
Karl Gram, BSc, MBA
http://www.gramonline.com
"blg" <anonymous@.discussions.microsoft.com> wrote in message
news:1C9AE715-5303-4FC6-ABD2-6ADCA7B6FF87@.microsoft.com...
> Is there a way to force all stored procedures to "recompile" or create a
new execution plan? What we are trying to do is find a way to quickly
identify all stored procedures that are invalid because of schema changes
like tables or columns dropped or altered.
> Thanks,
> BLG|||D'oh. Why didn't I think of that? :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Karl Gram" <NOSPAMkarl@.gramonline.nl> wrote in message news:uqRIwlqEEHA.4080@.TK2MSFTNGP09.
phx.gbl...
> Hi,
> Run the command DBCC FREEPROCCACHE to remove all compile plans from the
> procedure cache.
> --
> Karl Gram, BSc, MBA
> http://www.gramonline.com
>
> "blg" <anonymous@.discussions.microsoft.com> wrote in message
> news:1C9AE715-5303-4FC6-ABD2-6ADCA7B6FF87@.microsoft.com...
> new execution plan? What we are trying to do is find a way to quickly
> identify all stored procedures that are invalid because of schema changes
> like tables or columns dropped or altered.
>