Dynamic query to Union multiple databases

Question

Say I have the following databases in SQL Server 2008 R2

db1, db2, db3, db4, db5......dbn

each database has a table A which contains the columns C1,C2,C3

I can write the following Select statement on two databases to get the data across them:

Select C1,C2,C3
FROM db1.dbo.A

UNION ALL

Select C1,C2,C3
FROM db2.dbo.A

However if I have 50 databases on the same server I don't want to write a UNION ALL for each.

Can someone give me a script to do this? I can modify the script to exclude system databases myself.

Thanks

Answer 1

If you know the exact number of DBs:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'
UNION ALL
  SELECT C1,C2,C3
  FROM db' + CONVERT(VARCHAR(2), n) + '.dbo.A'
FROM 
(
  SELECT TOP (50) n = ROW_NUMBER() 
  OVER (ORDER BY [object_id])
  FROM sys.all_columns
) AS x;

SET @sql = STUFF(@sql, 1, 11, '') + ';';

PRINT @sql;
--EXEC sp_executesql @sql;

If you don't know that there are exactly 50, then this is probably better (it also allows you to exclude those that are not online):

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'
UNION ALL
  SELECT C1,C2,C3
  FROM ' + QUOTENAME(name) + '.dbo.A'
FROM sys.databases
WHERE state = 0
AND name LIKE N'db[0-9]%';

SET @sql = STUFF(@sql, 1, 11, '') + ';';

PRINT @sql;
--EXEC sp_executesql @sql;