title: How to UNION multiple databases as one source
body: <p>I have 40+ databases (linked servers) in my SSMS connection, each has the same schema, tables, columns but with unique data for each individual location. I'm looking for the best way to UNION them all together into a VIEW or Alias that would allow me to assign and ID based on the database/location and have a single source to query against essentially for future cross reference.</p>
  
  <p>For example, I would like to be able to query against a single common table between the databases that has columns [order], [item], [amount] and add an additional column [location] based on the database the data is coming from within the query. Such as:</p>
  
  <pre><code>SELECT *
  FROM [UnionTableSource]
  </code></pre>
  
  <p>Which shows results such as</p>
  
  <pre><code>Location | Order | Item | Amount
  1        | 1234  | 567  | $500
  2        | 1234  | 529  | $125
  3        | 1279  | 529  | $125
  3        | 1234  | 789  | $100
  </code></pre>