SQL如何从多个数据库中获取数据

Question

我有那个查询

USE database_A /*it will change: database_B, c , d etc..*/
select X.Name1, X.Name2, DB_NAME() AS [Current Database] 
from dbo.table1 A /*it will be the same table*/ 
left join
database_X.dbo.table_X X /*it will be the same database and table*/  on A.ID = X.ID_ID

我想得到一个结果，显示Name1和Name2的值以及创建它的上下文，例如：

当前数据库一个两个数据库_Aaaaddddatabase_A一个两个数据库_B一个两个数据库_B222112database_B[/tr>一个两个database_c

Answer 1

动态sql示例

-- list of DBs
create table dbs (dbn sysname);
insert dbs
values ('dbnameA'),('dbnameB'),('dbnameC');
-- build the query
declare @q varchar(Max);
select @q = string_agg(s, ' union all ')
from (
select 'select X.Name1, X.Name2, ''' + dbn + ''' AS [Current Database]' + 
' from ' + dbn + '.dbo.table1 A' + 
' left join database_X.dbo.table_X X on A.ID = X.ID_ID' s
from dbs) t
-- check it
select @q;
-- run it
exec (@q);