Query for combining results of same query running across multiple databases

Question

I wrote a query to run the same query across multiple databases and combine the results. While it seems plenty quick I was wondering if there is a better way to do this.

create table #serverlist(
 ID smallint IDENTITY(1,1),
 dbName varchar(50)
 )
create table #browsercounts(
 ID smallint IDENTITY(1,1),
 --Email varchar(50),
 Browser varchar(50),
 Counts int)
insert into #serverlist
select name from sys.databases
where name like '%Test2Portal%'
and name not like '%_Test%'
Declare @counter int, @rows int
set @counter = 1
set @rows = (select COUNT(dbName) from #serverlist)
while (@counter <= (@rows))
Begin 
 Declare @SQL varchar(1000)
 Declare @database varchar(50) = 
 (select dbName from #serverlist where ID = @counter)
 Select @SQL = 'select Browser, COUNT(Browser) as Counts from ' + 
 @database+ '.dbo.Session where Browser is not null group by Browser'
 insert into #browsercounts
 Exec (@SQL)
 set @counter += 1
End
Select * From #browsercounts
drop table #serverlist
drop table #browsercounts

Answer 1

You probably don't need any temp tables unless you need access to these temp tables across different stored procedures, here is how I would write it.

DECLARE @sql VARCHAR(MAX)
SELECT @Sql = COALESCE(@sql + ' UNION ALL ', '') + 'SELECT [' + name + '].dbo.Session.Browser, COUNT(['+name+'].dbo.Session.Browser) AS Counts FROM [' + name + '].dbo.Session WHERE [' + name + '].dbo.Session.Browser IS NOT NULL GROUP BY ['+name+']dbo.Session.Browser'
from sys.databases
where name like '%Test2Portal%' and name not like '%_Test%'
EXEC @sql --this will perform the select for you

hope this is helpful

• Andriy M: The OP seems satisfied with non-delimited names, judging by their query, but generally it is a good idea to delimit them in such cases, like you did. Only it would be safer to use QUOTENAME() rather than hardcoded [...] around the name. For, if there was a ] in the name, QUOTENAME() would escape it correctly, while with hardcoded brackets the name would remain unchanged and the query would then fail to execute.
• Bassam Mehanni: @AndriyM sounds like a good idea, never used QUOTENAME() before, didn't know you can use [ or ] in a database name but I just validated it and you are right

Answer 2

You could skip the while clause and execute it as one statement - something like

select @SQL = @SQL + ' select Browser, COUNT(Browser) as Counts from ' + 
 @database+ '.dbo.Session where Browser is not null group by Browser UNION ALL' from #serverlist
--you should get rid of the last union all statement in the string 
set @sql = left(@sql, len(@sql) - 10)
insert into #browsercounts
 Exec (@SQL)

--but just an idea ....

• Andriy M: Seems all right, only there should be a space before select (which I've added now).
• Dan Snell: So with this setup how do I actually get the database name for the @database variable? I had a select that incremented in the loop that pulled it for each one. I am not clear how that value is getting set in this.

Answer 3

I had this same issue today. I already had a table of the databases, so I wrote a program. It could be done with dynamic sql in the same way.

That said, I have huge regrets that my original design used multiple databases in the first place and maybe the reality is that the reason its not something readily doable is related to the fact that its not something that ideally needs to be done (just a thought).

void Main()
{
 string sql = @"
 print '@Name';
 print '[Lumos Labs ]->[Lumos Labs, Inc.]'; update Advertiser set Name='Lumos Labs, Inc.' where Name='Lumos Labs '
 print '[Emma Stein]->[Emma Stine]'; update Advertiser set Name='Emma Stine' where Name='Emma Stein'
 print '[Lieferheld GmbH]->[Lieferheld]'; update Advertiser set Name='Lieferheld' where Name='Lieferheld GmbH'
 print '[Monster]->[Monster Worldwide]'; update Advertiser set Name='Monster Worldwide' where Name='Monster'
 print '[Quinstreet / Surehits]->[QuinStreet LLC]'; update Advertiser set Name='QuinStreet LLC' where Name='Quinstreet / Surehits'
 print '[Eye Buy Now]->[T33ZE/Specs Optics/EyeBuyNow]'; update Advertiser set Name='T33ZE/Specs Optics/EyeBuyNow' where Name='Eye Buy Now'
 print '[T33ZE/Specs Optics/]->[T33ZE/Specs Optics/EyeBuyNow]'; update Advertiser set Name='T33ZE/Specs Optics/EyeBuyNow' where Name='T33ZE/Specs Optics/'
 print '[T33ZE]->[T33ZE/Specs Optics/EyeBuyNow]'; update Advertiser set Name='T33ZE/Specs Optics/EyeBuyNow' where Name='T33ZE'
 print '[SmartDate USD]->[Smartdate]'; update Advertiser set Name='Smartdate' where Name='SmartDate USD'
 print '[Vistaprint US]->[Vistaprint]'; update Advertiser set Name='Vistaprint' where Name='Vistaprint US'
 print '[Ultradiamond]->[UltraDiamonds.com]'; update Advertiser set Name='UltraDiamonds.com' where Name='Ultradiamond'
 print '[Tranzact Media]->[Remedy Health Media/MediZine]'; update Advertiser set Name='Remedy Health Media/MediZine' where Name='Tranzact Media'
 print '[MyCityDeal EUR]->[MyCityDeal]'; update Advertiser set Name='MyCityDeal' where Name='MyCityDeal EUR'
 ";
 foreach (var item in DADatabases.Skip(1))
 {
 string query = sql.Replace("@Name", item.Name);
 foreach (var line in query.Split('\n').Where(s => !string.IsNullOrWhiteSpace(s)))
 {
 try 
 {
 Console.WriteLine (">>" + line);
 using(var con = new SqlConnection(item.Connection_string))
 using(var cmd = new SqlCommand(line, con))
 {
 con.InfoMessage += (s, e) =>
 {
 Console.WriteLine (">" + e.Message);
 };
 con.Open();
 int i = cmd.ExecuteNonQuery();
 Console.WriteLine ("Rows Affected: " + i);
 }
 }
 catch(Exception e) 
 { 
 Console.WriteLine ("Exception: " + e.Message); 
 }
 }
 }
}