Query Across different database - Same table SQL Server 2012

Question

I Have a common table "User Activity" in about 200 different databases on different servers, is it possible to select rows from all databases for that table in one statement?

I have a list of those databases and servers they are on ina main database/table, can obtain by Select servername, dbname from DBases from CustomersList

Answer 1

Yes but you need to explicitly mention them all:

SELECT COl1,Col2,Col3 FROM Database1.schema.Table1
UNION ALL
SELECT COl1,Col2,Col3 FROM Database2.schema.Table1
UNION ALL
SELECT COl1,Col2,Col3 FROM Database3.schema.Table1
UNION ALL
.......
...
SELECT COl1,Col2,Col3 FROM Database200.schema.Table1

This is the kind of thing I would just build in Excel and paste into SSMS.

Then you might want to reconsider whether it's a good design to have this in 200 databases.

Answer 2

I am getting this error:

Incorrect syntax near 'ALL'

While I try to run your code.

I have address table in two databases so I modified your code as:

 DECLARE @tableName nvarchar(256) = 'dbo.Address'

 DECLARE @sql nvarchar(max) = ''

 SELECT @sql = @sql + 'SELECT * FROM [' + dbs.name + ']..[' + @tableName +        '] ' 
               + CASE WHEN @sql <> '' THEN 'UNION ALL ' ELSE '' END
 FROM sys.sysdatabases dbs where dbs.dbid in (7,8)

 and dbs.name NOT IN ('master', 'tempdb', 'msdb', 'model','SSISall')

 EXEC(@sql)

Answer 3

I suggest you to use this syntax:

DECLARE @tableName nvarchar(256) = 'Table1'
DECLARE @sql nvarchar(max) = ''

SELECT @sql = @sql + CASE WHEN @sql <> '' THEN 'UNION ALL ' ELSE '' END 
                   + 'SELECT * FROM [' + dbs.name + ']..[' + @tableName + '] '
FROM sys.sysdatabases dbs
WHERE dbs.name NOT IN ('master', 'tempdb', 'msdb', 'model')

EXEC(@sql)

You can easily optimize it to use in a stored procedure.