August 5, 2008 at 10:16 am
We have several SQL scripts we would like to run against multiple databases on the same server.
So far the coolest way I've come up with is to have a list of all of the databases I want to hit in a table, and loop thru that table, with the body of the loop dynamically generating a giant dynamic SQL string, and then running EXECUTE (@SQL) at the end.
This works fine, but it seems a little tacky. Has anyone else had a similar situation and come up with a better solution?
The Redneck DBA
August 5, 2008 at 10:34 am
You could use a cursor and while loop as in the following example to avoid the extra table you have with the list of table names in it:
use master
declare cur_databases cursor for
select name from sys.databases
declare @dbname varchar(100)
declare @cmd varchar(300)
open cur_databases
fetch cur_databases into @dbname
while @@fetch_status >= 0
begin
select @cmd = N'use ' + quotename(@dbname) + N' select count(*) from a_table'
exec (@cmd)
fetch cur_databases into @dbname
end
close cur_databases
deallocate cur_databases
or you could use sp_msforeachdb:
DECLARE @sqlstr VARCHAR(1000)
SET @sqlstr = 'use [?]'
SET @sqlstr = @sqlstr + 'select count(*) from a_table'
exec master..sp_msforeachdb @sqlstr
either way you need to have the string with the queries in it.
August 5, 2008 at 10:51 am
That's pretty much what I'm doing, just using regular loop instead of a cursor, and I have my execute outside of the loop after the sql is constructed for all databases. One of the things I want to do is have things all in one result set (I didn't say that in the original post). So I'm adding 'UNION' to the strings between databases.
The Redneck DBA
August 6, 2008 at 5:21 am
How about this...
DECLARE @bb AS varchar(max)
SET @bb = 'USE [?]; PRINT ''?''; '
SET @bb = @bb + '
DECLARE @aa AS varchar(max)
SET @aa = ''''
SELECT @aa = @aa + case WHEN @aa <> '''' THEN '' UNION ALL '' ELSE '''' END + '' '' + ''Select Count(*),''''''+ b.TABLE_NAME + '''''' FROM '' + a.name + ''.['' + b.TABLE_SCHEMA + ''].['' + b.TABLE_NAME + '']'' ' +
'FROM sys.sysdatabases a
INNER JOIN INFORMATION_SCHEMA.TABLES b ON b.Table_Catalog = a.name
WHERE b.TABLE_TYPE <> ''VIEW'' AND TABLE_SCHEMA <> ''sys'' AND TABLE_NAME NOT LIKE ''#%'';
PRINT @aa;
Exec(@aa)'
SET @bb = 'USE [?]; PRINT ''?''; ' + @bb
EXEC sp_MSForEachDB @bb
I think it solves your purpose...
Atif Sheikh
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply