title: Selecting data that could reside in many databases
body: <p>This is so complicated (in my head at least) that I couldn't title my question properly.</p>
  
  <p>I have hundreds of databases (one for each <code>Supplier</code>) that I need to connect to from a central database that contains <code>Order</code>, <code>Customer</code>, and <code>Supplier</code> tables.</p>
  
  <p>Basically the <code>Suppplier</code> table in the central database is like this:</p>
  
  <pre><code>SupplierID | SupplierName | SupplierDatabaseName
  1            Nestle         Nestle005
  2            Mars           Mars001
  </code></pre>
  
  <p>It holds details about a Supplier and most importantly what their Database Name is called.</p>
  
  <p>When a customer places an Order it goes into the central database Order table like this:</p>
  
  <pre><code>OrderID | CustomerID | SuppplierID | ProductID | Qty
  1         1            2             100         5  
  </code></pre>
  
  <p>In the Supplier's database such as Mars001 there is a table for that company to manage information about their products like such:</p>
  
  <pre><code>ProductID | ProductName
  100         Skittles
  </code></pre>
  
  <p>What I need to do is return a list of orders by a customer, along with the <code>ProductName</code>. This means I have to: </p>
  
  <ol>
  <li>Select everything from the central database's <code>Order</code> table</li>
  <li>Somehow dynamically provide the Supplier's database name to be used in a JOIN statement to get the ProductName out of the Products table in that database</li>
  </ol>
  
  <p>The final output should look like this:</p>
  
  <pre><code>OrderID | CustomerID | SupplierID | ProductID | ProductName
  1         1            2            100         Skittles
  </code></pre>
  
  <p>I understand dynamic sql to some extent, and I can dynamically supply a database name using a parameter. But that only works when the parameter value will stay the same in a query. This is more complex than that.</p>
  
  <p>Could anyone please a suggest a way of tackling this? Perhaps its not possible to do in a single query?</p>