A script to re-compile stored procedures

Hello all,
Is there anyone who has a script to generate a script to re-compile all
stored procedure under a database? (or a script to re-compile all stored
procedures under a database.)
I have to re-compile all stored procedure under a database.
I will appreciate it if you post it.
Thanks in advance,
Do.
--
Message posted via http://www.sqlmonster.comSee if this helps:
use northwind
go
declare @.sql nvarchar(4000)
declare @.rs sysname
declare @.rn sysname
declare procedures_cursor cursor local fast_forward
for
select
routine_schema,
routine_name
from
information_schema.routines
where
routine_type = 'procedure'
and objectproperty(object_id(quotename(routine_schema) + '.' +
quotename(routine_name)), 'IsMSShipped') = 0
open procedures_cursor
while 1 = 1
begin
fetch next from procedures_cursor into @.rs, @.rn
if @.@.error != 0 or @.@.fetch_status != 0 break
set @.sql = N'exec sp_recompile ''' + quotename(@.rs) + N'.' + quotename(@.rn)
+ N''''
exec sp_executesql @.sql
end
close procedures_cursor
deallocate procedures_cursor
go
AMB
"Do Park via SQLMonster.com" wrote:
> Hello all,
> Is there anyone who has a script to generate a script to re-compile all
> stored procedure under a database? (or a script to re-compile all stored
> procedures under a database.)
> I have to re-compile all stored procedure under a database.
> I will appreciate it if you post it.
> Thanks in advance,
> Do.
> --
> Message posted via http://www.sqlmonster.com
>