Query multiple and varying databases on same server dynamically?

[lmohr79]

Due to client considerations, I have a project that has multiple databases (all same schema) on a single server, having 1 additional database with "master" data (client name, contact info, etc.) Users need to access data from across multiple databases. I know that I can use UNION if I know which databases to query:

SELECT c1.OrderNo, c1.OrderDate, m.ClientName
FROM Client1.dbo.customer c1
INNER JOIN on Master.dbo.client m
ON c1.clientID = m.clientID

UNION ALL

SELECT c2.OrderNo, c2.OrderDate, m.ClientName
FROM Client2.dbo.customer c2
INNER JOIN on Master.dbo.client m
ON c2.clientID = m.clientID


My challenge is to allow users to dynamically select which clients (databases) to access, allowing for a few as 1 to as many as 10 or more - number of clients/databases will continue to grow and combinations that the users want to access will be 'limitless' - I'd like to do this on the database server rather than in the front end (asp.net application) - is this possible? If so, I'd appreciate any ideas you might have.

I'm currently developing in 2005, but will be moving to 2008R2 in the near future.

Thanks for any ideas.

 

[markros]

Yes, you can pass a table of available DBs with the same schema (if you're using SQL Server 2008 you can pass data table) and then you can generate this statement dynamically.

Take a look at this blog post explaining the concept

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/how-to-get-information-about-all-databas

PluralSight Learning Library