May 21, 2008 at 5:26 pm
Hi guys, does anyone here knows how write a script that can switch between database context?
for example i want to exec a query in a loop with diff database cont for each loop.
loop1
use db1
go
query
end
loop2
use db2
query
end
May 22, 2008 at 2:38 am
How about using dynamic sql to generage your query within the loop?
For example:
loop
begin
set @sql_str='select * from mydatabase.dbo.mytable'
exec sp_executesql @sql_str
end
Or have i misunderstood your question?
Matt
May 23, 2008 at 12:23 am
sorry for not being so clear, i want a script that will dynamically select database in which other scripts will execute.
i wanted this in order to exec DBCC scripts for multiple databases
May 23, 2008 at 9:02 am
This is what he is saying:
set @sql_str='select * from mydatabase.dbo.mytable'
If you fully qualify the tablename prefixed with the database, you can execute against that table regardless of the connection context you're running in. So, loop through all of your dbs replacing the "mydatabase" above with your variable.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply