July 13, 2006 at 10:05 pm
I have about 50+ databases in 3 servers. I want to run a series of commands on each database... the database names I am interested in are in a table.. how can I change the database context - the current database - dynamically in a loop...
i.e. USE @databasename
USE does not take a variable parameter...
July 14, 2006 at 2:34 am
I'm not too sure you can do this.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
July 14, 2006 at 3:16 am
Depending on what commands you are trying to execute against the databases in your list, something like this might be what you're looking for:
declare @db sysname
declare @sqlstring nvarchar(100)
set @db = N'MyDB'
set @sqlstring = N'select * from ' + @db + '.dbo.MyTable'
print @sqlstring --Comment out when you are satisfied this produces the correct command
exec sp_executesql @sqlstring
John
July 14, 2006 at 3:37 am
yes you can issue cross database commands -- I think you can handle the use command by executing script files using osql - which you can do from QA / jobs but it's not very elegant.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
July 14, 2006 at 9:02 am
Try using sp_MSforeachdb (be aware, it is undocumented).
More information here: http://www.transactsql.com/html/sp_MSforeachdb.html
July 16, 2006 at 2:04 am
You can change databases dynamically within the context of an EXEC statement. If the sql commands you wish to execute are the same for each database, this is fairly straightforward:
EXEC ('use ' + @DBName + '<sql commands>')
The SQL commands can also be put in a variable if you choose:
EXEC ('use ' + @DBName + ' ' + @SQLCommands)
Put either of these in your loop against the db names and this should solve your problem.
Scott Thornburg
July 17, 2006 at 2:05 am
EXEC ('use ' + @DBName + '<sql commands>')
The SQL commands can also be put in a variable if you choose:
EXEC ('use ' + @DBName + ' ' + @SQLCommands)
Put either of these in your loop against the db names and this should solve your problem."
Scott is absolutely right. Remember that the database context change lasts only as long as the EXEC command. Therefore the following will not work:
EXEC ('use ' + @DBName)
EXEC @SQLCommands
John
July 17, 2006 at 7:02 am
You could use xp_execresultset to set database context, e.g.
DECLARE @db varchar(255), @sql nvarchar(4000)
SET @sql = 'SELECT ''SELECT [name] FROM sysobjects WHERE type = ''''U'''''''
SET @db = ''
WHILE @db IS NOT NULL
BEGIN
SELECT @db = MIN([databasename]) FROM
WHERE [databasename] > @db
IF @db IS NULL BREAK
EXEC master.dbo.xp_execresultset @sql,@db
END
However this will not allow cross server access, for that you would have to use dynamic sql with linked server(s)
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply