Dynamically changing databases in SQL Server 2000

Question

At work we have a number of databases that we need to do the same operations on. I would like to write 1 SP that would loop over operations and set the database at the beginning of the loop (example to follow). I've tried sp_executesql('USE ' + @db_id) but that only sets the DB for the scope of that stored procedure. I don't really want to loop with hard coded database names because we need to do similar things in many different places and it's tough to remember where things need to change if we add another DB.

Any thoughts>

Example:
DECLARE zdb_loop CURSOR FAST_FORWARD FOR SELECT distinct db_id from DBS order by db_id

OPEN zdb_loop FETCH NEXT FROM zdb_loop INTO @db_id

WHILE @@FETCH_STATUS = 0 BEGIN USE @db_id

--Do stuff against 3 or 4 different DBs

FETCH NEXT FROM zdb_loop INTO @db_id

END

CLOSE zdb_loop DEALLOCATE zdb_loop

Answer 1

You can use the stored procedure sp_MSforeachdb for this:

This example will perform a database backup, then a "DBCC CHECKDB" against each database:

declare @cmd1 varchar(500)
declare @cmd2 varchar(500)
declare @cmd3 varchar(500)
set @cmd1 =
   'if ''?'' <> ''tempdb'' print ''*** Processing DB ? ***'''
set @cmd2 = 'if ''?'' <> ''tempdb'' backup database ? to disk=''c:\temp\?.bak'''
set @cmd3 = 'if ''?'' <> ''tempdb'' dbcc checkdb(?)'
exec sp_MSforeachdb @command1=@cmd1, 
                    @command2=@cmd2,
                    @command3=@cmd3