title: I need to create a view that unions all tables based on their prefix (new tables added monthly)
body: <p>The integration software that my client is using is pulling over ISAM files from a RMS system and creating lots of tables that all have identical schemas, but nonetheless the software they use doesn't support merging them together.  </p>
  
  <p>Here's some example table names:</p>
  
  <ul>
  <li>SOME_TABLE_XYZ_2012OCT</li>
  <li>SOME_TABLE_ABC_2012OCT</li>
  <li>SOME_TABLE_LMN_2012OCT</li>
  <li>SOME_TABLE_XYZ_2012NOV</li>
  <li>SOME_TABLE_ABC_2012NOV</li>
  <li>SOME_TABLE_LMN_2012NOV</li>
  </ul>
  
  <p>I need to roll these all up into a single view named SOME_TABLE, with the data union all'd together.  It must be dynamic because there can be new 3 char codes and obviously, monthly new tables being created.</p>
  
  <p>Ideally I'd like a SP that creates a view named SOME_TABLE, then I can query off of SOME_TABLE to do the work that I need on it (scheduled ETL into a sql server 2012 data warehouse).  I know that I could just have the SP return a cursor, but creating the view gives me flexibility to make it an indexed view if I need for performance, and ease of use from other ETL tools.</p>
  
  <p>I figure on querying sys.tables (or whatever it is), and working from there to build up a sql statement for a create view.</p>
  
  <p>Any other bright ideas on how to approach this?</p>