The following code is very useful in situations where one has to run a single sql statement against all/some of the system objects. The below example can be used to generate a code that re-compiles all the stored procedures in a given d/b. It just prints out the required code, so open a new query window and run the query to actually recompile each stored procedure.
set quoted_identifier off
declare @procname varchar(100)
declare @string varchar(1000)
declare cursor1 cursor for
select name from sysobjects where type='p'
open cursor1
fetch next from cursor1 into @procname
while (@@fetch_status = 0)
begin
select @string="sp_recompile "+@procname +""
print @string
fetch next from cursor1 into @procname
end
close cursor1
deallocate cursor1
set quoted_identifier off
declare @procname varchar(100)
declare @string varchar(1000)
declare cursor1 cursor for
select name from sysobjects where type='p'
open cursor1
fetch next from cursor1 into @procname
while (@@fetch_status = 0)
begin
select @string="sp_recompile "+@procname +""
print @string
fetch next from cursor1 into @procname
end
close cursor1
deallocate cursor1
