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.
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.
Query Multiple DBs on the Same Server
Posted - 09/17/2012 : 19:33:43
