HowTo Union a given query across n duplicate databases

Question

I can't seem to accomplish my task of having T-SQL return a recordset that is Union of any given select query against n databases. I am working in an environment that has several databases with duplicate structures within the same SQL instance. The passed in SQL will be a select statement and I need the SP to return a Read-Only recordset. The code I think shows what I am trying to do. I can use any other approach as long as I can pass in a sql statement and receive back the Unioned result.

Alter PROCEDURE DBUnionSelect(@UserQuery varchar(25) ) AS
declare @dbName varchar(25), @strSQL varchar(200)
declare t_cursor cursor for
   select dbName from DBlist
for read only

open t_cursor
fetch next from t_cursor into @dbName
while @@fetch_status = 0 begin
   USE @dbName
   EXEC (@UserQuery)         --need to somehow union all EXECs together

   fetch next from t_cursor into @dbName
end

deallocate t_cursor
RETURN  

Answer 1

There is really no easy way to do this. If you go the EXEC route, you have to create a temporary table to hold the results and then you can use INSERT...EXEC like:

create table #results (....)

while (....)
begin
   set @spname = quotename(@dbName) + N'.dbo.sp_executesql'
   insert into #results
   exec @spname @UserQuery;
end

Answer 2

Hi UJ
I did try the temp table approach. The problem I had was with the necessity of the Create Table step. I want to pass in any SQL select statement so the SP can't have a fixed table definition.
Thanks for the idea.
Paul

Answer 3

If the resultset is not known beforehand then it is not possible to do this easily. You could however do the following (pseudo-code only):

1. Determine meta-data of query first from the client (using say SET FMTONLY ON)
2. Create a temporary table with the determined schema
3. Call your SP that does the EXEC of the query in each DB and pipe results to temporary table:

insert into #results
exec yoursp

-- this SP can produce multiple results and as long as they are of same shape they will get inserted into the temporary table

You should avoid writing these type of generic code as far as possible. It only complicates development and makes management/debugging a pain. You are probably better off by executing the query in each database from the client directly. It is easy to write such helpers on the client and it is more optimized that way. (You could even fetch results from multiple databases simultaneously and improve performance)