Querying data from multiple databases

Question

I have dynamic databases being added everyday to my system.

the name always follows this pattern:

NAME_YYYYMMDD1200

and the tables inside it are the same.

How can I query [SELECT statement] all column for myTable for the last 5 days databases?

For example, if today is 10-OCT-2013, I query all columns for myTable for these DBs:

NAME_201310101200
NAME_201310091200
NAME_201310081200
NAME_201310071200
NAME_201310061200

I try SELECT * FROM NAME_*.myTable but it oubviusly doesn't work.

How can I have a [SELECT] query DB name dynamic/built on the fly?

Thanks,

ceth · Accepted Answer · 2014-07-04 11:53:42Z

3

You can execute your query in each database sp_msForEachDb.

You can filter databases like this:

EXEC sp_MSforeachdb
@command1='IF ''?''
IN (''AdventureWorks2012'',''AdventureWorks2012_test'')
BEGIN
       select * from [?].sys.schemas
END'

edited Jul 4, 2014 at 11:53

answered Jul 4, 2014 at 11:25

ceth

45.5k63 gold badges191 silver badges300 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

D. Caan Over a year ago

Thanks for the answer. How would my query look like? Do you have an example on a simple select on all DBs?

ceth Over a year ago

I have a link in my answer. Here is an example from this link "EXEC sp_MSForEachDB 'select * from [?].sys.schemas'"

D. Caan Over a year ago

This won't work because of the sys DBs. It will try to find a table called myTable there but there's no such a table there. How can I filter the tables names?

Pang Over a year ago

Link in answer is dead.

Collectives™ on Stack Overflow

Querying data from multiple databases

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related