Geek Answers Handbook

Querying through a different database - same SQL Server 2012 table

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 this table in one expression?

I have a list of the databases and servers on which they are located in the main database / table, you can get Select the server name, dbname from the database from the CustomersList list

+5
source share
3 answers

Yes, but you need to explicitly list 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 what I would just like to create in Excel and paste into SSMS.

Then you may need to reconsider whether it is good to have this project in 200 databases.

+5
source

I suggest you 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 for use in a stored procedure.

+1
source

I get this error:

Invalid syntax next to 'ALL'

While I'm trying to run your code.

I have an address table in two databases, so I changed 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)
+1
source

More articles:


All Articles