Recompile ALL sprocs in Dbase.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Recompile ALL sprocs in Dbase.

Author

Topic

mdelgado
Posting Yak Master

141 Posts

Posted - 2002-10-15 : 16:34:38

Is there a system stored procedure that can recompile all stored procedures in all my Dbases?

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-10-15 : 17:13:35

You can use sp_recompile e.g.


declare @sproc varchar(128)

declare sproc_cur cursor local fast_forward
for
select SPECIFIC_NAME 
from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_TYPE='PROCEDURE'
and OBJECTPROPERTY(object_id(SPECIFIC_NAME),'IsMSShipped')=0

open sproc_cur

fetch next from sproc_cur into @sproc

while @@fetch_status=0
begin
        exec sp_recompile @sproc
        fetch next from sproc_cur into @sproc
end

-- TH-TH-TH-THAT'S IT!
close sproc_cur
deallocate sproc_cur

HTH
Jasper Smith

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-15 : 17:35:38

Or use DBCC FLSUHPROCINDB (it's undocumented, don't feel bad unless you have one of Ken Henderson's books):

DECLARE @db int
SET @db=DB_ID('myDatabase')
DBCC FLUSHPROCINDB(@db)

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-10-15 : 19:28:54

I have both

However I'm a bit hardline when it comes to undoc'ed commands - if there is a way to do it using system stored procedures/views etc then I'd advocate using them - they are less likely to break in future releases (not a problem in this scenario probably) - especially if you can get someone else to write it for you

HTH
Jasper Smith

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources