What is the canonical way to select same query over multiple db's and union them?

Question

I have 9 databases in my SQL Server setup. Let's call them ONE_DB, TWO_DB etc. A quite common task I find myself doing is extracting some data from all nine instances (usually quite small tables like config etc) with the same query for the same table but over all databases. I would love a union all select interesting_col from *.dbo.tableX

I have come up with one way to do this, based on other questions here on SO, but I am not quite content with this solution. Lets say I only want one column from one table, I first run the SQL query below...

declare @db varchar(30)
declare @db_list varchar(200)
set @db = ''
set @db_list = 'ONE_DB,TWO_DB,THREE_DB,FOUR_DB,FIVE_DB,SIX_DB,SEVEN_DB,EIGHT_DB,NINE_DB'

while len(@db_list) > 0
begin
  set @db = left(@db_list, charindex(',', @db_list+',')-1);
  set @db_list = stuff(@db_list, 1, charindex(',', @db_list+','), '');
  exec ( 'use '+@db+'; select tx.interesting_columns from dbo.TableX as tx;')
end

... and then i copy-paste the 9 result sets to Excel manually. Of course I normally do some more processing in the select statement such as different joins, aggregations, case-clauses etc. but I guess you get the point.

This process is quite okay, but the code is not very easy to read when there are more complex select statements. It is also quite difficult for a new person to read this code as the syntax highlighter interprets all the interesting stuff (the select statement) as a string.

What is the "good" way to deal with this?

Answer 1

With the help of a Parse/Spit function and using a "token" for the DB Name

Declare @db_list varchar(max) = 'ONE_DB,TWO_DB,THREE_DB,FOUR_DB,FIVE_DB,SIX_DB,SEVEN_DB,EIGHT_DB,NINE_DB'
Declare @cmd     varchar(max) = ';Use <<dbname>>; select interesting_col from dbo.tableX'
Declare @SQL     varchar(max) = '>>>'

Select @SQL = Replace(Replace(@SQL+replace(@cmd,'<<dbname>>',QuoteName(RetVal))+char(13),'>>>Union All',''),'>>>','')
 From [dbo].[udf-Str-Parse](@db_list,',')

Exec(@SQL)

Example 1

Declare @cmd     varchar(max) = ';Use <<dbname>>; select interesting_col 

The SQL Generated is

;Use [ONE_DB];select interesting_col from *.dbo.tableX
;Use [TWO_DB];select interesting_col from *.dbo.tableX
;Use [THREE_DB];select interesting_col from *.dbo.tableX
;Use [FOUR_DB];select interesting_col from *.dbo.tableX
;Use [FIVE_DB];select interesting_col from *.dbo.tableX
;Use [SIX_DB];select interesting_col from *.dbo.tableX
;Use [SEVEN_DB];select interesting_col from *.dbo.tableX
;Use [EIGHT_DB];select interesting_col from *.dbo.tableX
;Use [NINE_DB];select interesting_col from *.dbo.tableX

Example 2 - Combine Results

Declare @cmd     varchar(max) = 'Union All select DBName=''<<dbname>>'',interesting_col from <<dbname>>.dbo.tableX'

The SQL Generated

 select DBName='[ONE_DB]',interesting_col from [ONE_DB].dbo.tableX
Union All select DBName='[TWO_DB]',interesting_col from [TWO_DB].dbo.tableX
Union All select DBName='[THREE_DB]',interesting_col from [THREE_DB].dbo.tableX
Union All select DBName='[FOUR_DB]',interesting_col from [FOUR_DB].dbo.tableX
Union All select DBName='[FIVE_DB]',interesting_col from [FIVE_DB].dbo.tableX
Union All select DBName='[SIX_DB]',interesting_col from [SIX_DB].dbo.tableX
Union All select DBName='[SEVEN_DB]',interesting_col from [SEVEN_DB].dbo.tableX
Union All select DBName='[EIGHT_DB]',interesting_col from [EIGHT_DB].dbo.tableX
Union All select DBName='[NINE_DB]',interesting_col from [NINE_DB].dbo.tableX

Example 3 - Execute Stored Procedure

Declare @cmd     varchar(max) = ';Use <<dbname>>; exec SomeStoredProcedure'

The SQL Generated

;Use [ONE_DB]; exec SomeStoredProcedure
;Use [TWO_DB]; exec SomeStoredProcedure
;Use [THREE_DB]; exec SomeStoredProcedure
;Use [FOUR_DB]; exec SomeStoredProcedure
;Use [FIVE_DB]; exec SomeStoredProcedure
;Use [SIX_DB]; exec SomeStoredProcedure
;Use [SEVEN_DB]; exec SomeStoredProcedure
;Use [EIGHT_DB]; exec SomeStoredProcedure
;Use [NINE_DB]; exec SomeStoredProcedure

The UDF if needed

CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (  
    Select RetSeq = Row_Number() over (Order By (Select null))
          ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
    From (Select x = Cast('<x>'+ Replace(@String,@Delimiter,'</x><x>')+'</x>' as xml).query('.')) as A 
    Cross Apply x.nodes('x') AS B(i)
);
--Select * from [dbo].[udf-Str-Parse]('Dog,Cat,House,Car',',')
--Select * from [dbo].[udf-Str-Parse]('John Cappelletti was here',' ')

Answer 2

Consider using the Server Group feature of SQL Server Management Studio to execute an ad-hoc query against multiple servers (or multiple databases on the same server in your case) and automatically union the results. This can be done as follows.

1) Right-click on the Local Server Groups node in SSMS Object Explorer, select New Server Group, and give it a mnemonic name (e.g. DevServerDatabases).

2) Right-click on the new group name, select New Server Registration, specify:

• server name = name of server
• registered server name = name of database
• database name (connection properties tab) = name of database
• repeat step 2 for each database

Once all databases are added to the group, right-click on the group name and select New query. Enter the ad-hoc query in the window and execute. The result set will contain the union of all the query results plus the database name (Server Name column), which can be turned on or off ((Tools-->Options-->Query Results-->SQL Server-->Multiserver Results).

Answer 3

Use SSIS to extract data from multiple databases. In SSIS you can merge and otherwise transform the data and output it to excel. You can also schedule SSIS to run automatically.