title: Rewrite a UNION view as a dynamic view
body: <p>I've been working on this for days and I can't find a proper solution, I'm hoping someone can help.</p>
  
  <p>We have several database servers (SQL Server) with identical structures, connected by linked servers.  We have a bunch of views that union data across all the servers:</p>
  
  <pre><code>CREATE VIEW Things_view AS
    SELECT id, thing
      FROM server1.database1.dbo.stuff
    UNION ALL
    SELECT id, thing
      FROM server2.database2.dbo.stuff
    UNION ALL
    SELECT id, thing
      FROM server3.database3.dbo.stuff
  </code></pre>
  
  <p>There are several problems with this:</p>
  
  <ul>
  <li>When we add servers, we have to modify
  views.</li>
  <li>Our development environments need
  the same number of databases and fake linked
  servers so we can install these
  views.</li>
  <li>Some of the servers <em>do not
  include</em> data from some of the other servers.  Today we do this with customized views on those servers but that's a deployment nightmare.</li>
  </ul>
  
  <p>So I was hoping to rewrite this dynamically.  The goals are:</p>
  
  <ul>
  <li>Allow development environments to
  contain a different number of databases.  They are likely to
  have 2 or more databases on a single server.</li>
  <li>Provide a way for each server to
  specify which other servers to
  include in the results.</li>
  <li>Minimize the complexity so the
  solution remains somewhat
  maintainable.</li>
  <li>Create one solution that can be
  source controlled and used on all
  servers.</li>
  </ul>
  
  <p>And most importantly:</p>
  
  <ul>
  <li><strong>Expose it as a view with the same
  name so we don't have to rewrite our
  entire application.</strong></li>
  </ul>
  
  <p>Of course, I can't do dynamic SQL in a view or function.  I tried rewriting this as a stored procedure, then using the <a href="http://blogs.technet.com/b/wardpond/archive/2005/08/01/408502.aspx" rel="nofollow">OPENROWSET trick</a> to query the stored proc from a view.  That didn't work because I had to fully qualify the proc with the database name, which would require more dynamic SQL (because the database names are different).  Then I thought about fooling around with synonyms on the master database but that violates the maintenance and source control goals.</p>
  
  <p>I'm perfectly OK with creating a new table with server names and database names and inclusion flags if needed.  In fact, that is ideal because it centralizes the configuration for each environment.</p>
  
  <p>I've been completely baffled by this and now I'm reaching out to you, Internet, in a last-ditch plea for help!</p>