sp_recompile on all dbs - SQL Server Forums

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

SQL Server Development (2000)

sp_recompile on all dbs

Author

Topic

skillile
Posting Yak Master

208 Posts

Posted - 2003-11-11 : 14:38:08

I am trying to do a sp_recompile on all my dbs without generating scripts for each on.
Is their a way I can get this to work.

DECLARE @sql varchar(1000)
SET @sql = ''
SET @sql = @sql + 'USE '+ 'Portal ' + char(13) + char(10) + ' GO' -- put the recompile script here
EXEC(@sql)

so I can switch between DB's and run the final command. I would end up with something like.

use db1
go
sp_recompile(table)
sp_recompile(table1)
usedb2
go
sp_recompile(table)
sp_recompile(table1)

etc...

thanks

slow down to move faster...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-11 : 14:43:04

Just fully qualify your objects instead of switching databases. I do not believe that you'll be able to switch databases anyway.

sp_recompile(DB1.dbo.SP1)
sp_recompile(DB2.dbo.SP1)

Tara

skillile
Posting Yak Master

208 Posts

Posted - 2003-11-11 : 14:47:53

BOL says
Remarks
sp_recompile looks for an object in the current database only.

I tried the fully qualified. I don't believe it works.

I thought I could use something like:

sp_foreachdb
sp_foreachtable
and loop through them.

..any help, thanks

slow down to move faster...

skillile
Posting Yak Master

208 Posts

Posted - 2003-11-11 : 14:50:18

this however may work,

EXEC portal.dbo.sp_recompile 'portal.dbo.table'

slow down to move faster...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-11-11 : 19:46:00

DECLARE @sql varchar(8000)
SELECT @sql=''
SELECT @sql=@sql + 'DBCC FLUSHPROCINDB(' + cast(dbid as varchar) + '); ' FROM master..sysdatabases WHERE dbid>4
EXEC(@sql)

That will force a recompilation on all sprocs in each db (except the system DB's).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources