Dynamically changing databases in SQL Server 2000
- by spuppett
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
