SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Query Multiple DBs on the Same Server
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

wldodds
Starting Member

20 Posts

Posted - 09/17/2012 :  19:33:43  Show Profile  Reply with Quote
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

USA
1974 Posts

Posted - 09/17/2012 :  19:39:26  Show Profile  Visit chadmat's Homepage  Reply with Quote
You would have to build a dynamic sql string. You could also use sp_msforeachdb

-Chad
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 09/17/2012 :  19:40:38  Show Profile  Reply with Quote
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/

Go to Top of Page

wldodds
Starting Member

20 Posts

Posted - 10/02/2012 :  11:48:53  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/02/2012 :  12:59:48  Show Profile  Reply with Quote
for dbnames you need to put ? as place holder


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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000