Get data from multiple databases at once

Question

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?

Not so bad, but please, please, please write "I" in uppercase... :-) — peterh
– peterh, Commented Jun 21, 2018 at 10:19
Believe me: If you would feel, how does it look, you wouldn't ever type in an "i" in your entire life. — peterh
– peterh, Commented Jun 21, 2018 at 10:42

Kin Shah · Accepted Answer · 2018-06-21 12:30:15Z

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

Peter Vandivier · Accepted Answer · 2018-06-21 12:42:02Z

1

One of the methods you can try is sp_msforeachdb. Making some assumptions about your datatypes in the @table below...

declare @results table (
    prac_id int, 
    prac_name nvarchar(100), 
    prace_surname nvarchar(100), 
    prac_IDcard nvarchar(100), 
    prac_workplace nvarchar(100)
);

insert @results
exec sp_msforeachdb N'
use [?]

if left(''?'',6) = ''CDNXL_'' -- only execute the query against databases 
                              -- that match the naming pattern
    and ''?'' <> ''CDNXL_Configuration''
begin
    select
        prac_id, 
        prac_name, 
        prace_surname, 
        prac_IDcard, 
        prac_workplace
    from prac_nag.[?]
    where prac_IDcard = @parameter; 
end;
';

select * from @results;

edited Jun 21, 2018 at 12:42

answered Jun 21, 2018 at 10:08

Peter Vandivier

5,9511 gold badge25 silver badges50 bronze badges

THX :) it's work perfectly. For another, I changed syntax - 'CDNXL_' to "CDNXL_" and from prac_nag.[CDNXL_?] to prac_nag.[?]

Kamil W.
– Kamil W.

2018-06-21 10:53:21 +00:00
Commented Jun 21, 2018 at 10:53
1

fair warning using ms_foreachdb - sqlblog.org/2010/12/29/…

Kin Shah
– Kin Shah

2018-06-21 12:11:47 +00:00
Commented Jun 21, 2018 at 12:11

Add a comment |

Stack Exchange Network

Get data from multiple databases at once

2 Answers 2

Your Answer

Hot Network Questions

Get data from multiple databases at once

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions