SQL How to get data from many datababases?-CodePudding

I have that query

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

I would like to get a result that shows the values of Name1 and Name2 and the context in which it was made, e.g .:

Name1	Name2	Current Database
One	Two	database_A
aaa	ddd	database_A
One	Two	database_B
One	Two	database_B
222	1112	database_B
One	Two	database_c

CodePudding user response：

Dynamic sql example

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