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
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;
