Query Multiple DBs on the Same Server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Query Multiple DBs on the Same Server

Author

Topic

wldodds
Starting Member

20 Posts

Posted - 2012-09-17 : 19:33:43

I have a table which stores the db names where I would like to create a sql view that when run goes to each db that is listed in this table and retrieves the data.

For Example:
Table db_names has column dbid
Table db_names is stored in db 1234mstr

Each db listed in db_names has a table t1

I'm trying to create a query like:

select dbid as Database_Name, t.*
from [db_names].[dbid]..t1

The results would then display the db name and records from the table in that database.

I realize I could do a union but then every time a db is added to the sql environment I would have to update the union, this way I can just add the db to this one table or remove one if I don't want it included any longer.

Any ideas on how to do something like this?

chadmat
The Chadinator

1974 Posts

Posted - 2012-09-17 : 19:39:26

You would have to build a dynamic sql string. You could also use sp_msforeachdb

-Chad

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-17 : 19:40:38

you need to use EXEC sp_msforeachdb system stored procedure

see
http://weblogs.sqlteam.com/joew/archive/2008/08/27/60700.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

wldodds
Starting Member

20 Posts

Posted - 2012-10-02 : 11:48:53

Ok, I read through that archive and it looks like it will work. I'm now struggling with the propery syntax to only run the select statement against specific db's.

Here is what I have:

EXEC sp_MSforeachdb 'USE ['+(Select INTERID
from dblist..dbname
where interid not in (
select INTERID
from dblist..dbname
WHERE (CMPNYNAM like '%TEST%'
or CMPNYNAM like '%HISTORICAL%')))+'];' select vendorid, vendorname, checknbr, trxamount from payables where checkdate = getdate()

Basically I'm trying to get the db's to run this against and then run the select query. I obviously have something messed up in the syntax because the single quote around the select for db's doesn't even work.

Any help is greatly appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-02 : 12:59:48

for dbnames you need to put ? as place holder

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources