Sql-server – Get data from multiple databases at once

sql servert-sql

I need to UNION data from tables with the same name in multiple databases. All databases have the same prefix: CDNXL_(NAME OF DB)

How would I execute this query against more than one database at at time?

SELECT 
    prac_id, 
    prac_name, 
    prace_surname, 
    prac_IDcard, 
    prac_workplace
From prac_nag.CDNXL_ (NAME OF DB) 
where prac_IDcard = @parameter

Can this be done with 'IF' or 'WHILE'?

— Edit
Now I can see my mistake.

I have got 46 Databases with prefix CDNXL_ but one is DB with CDNXL_Configuration, and doesn't have got table was I wanna. How can I skip/ignore this DB?

Best Answer

No need to use cursor or undocumented methods. You can just use dynamic tsql.

declare @sqltext nvarchar(max) = N''
declare @parameter varchar(max) = 'define what you want to search here'
select @sqltext += '
SELECT 
    prac_id, 
    prac_name, 
    prace_surname, 
    prac_IDcard, 
    prac_workplace
From '+name+'.dbo.prac_nag 
where prac_IDcard = '''+@parameter+''''
FROM sys.databases
WHERE OBJECT_ID(QUOTENAME(name) + '.dbo.prac_nag', 'U') IS NOT NULL -- will return only if the table exists
and name like 'CDNXL_%' -- filter your dbs out
print @sqltext
-- once your review the output, uncomment out below
--exec sp_executesql @sqltext
Related Question