Query for combining results of same query running across multiple databases 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 &lt;= (@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
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