title: SQL- UNION ALL a large number of tables
body: <p>I have a large number of tables (some thousands) containing similar data. I would like to run some reports from these. The table names are similar, so I can get a list of table names. </p>
  
  <p>I will likely merge these tables in the future, should be trivial once the select works.</p>
  
  <pre><code>--Getting a list of all tables 
  select TABLE_NAME
  FROM INFORMATION_SCHEMA.TABLES
  WHERE TABLE_NAME LIKE 'TableNamePrefix%'
  ORDER BY TABLE_NAME
  </code></pre>
  
  <p>To combine data, I can use UNION ALL, but not sure about how to set up the WHILE/cursor so that the list does not need to be constantly updated.</p>
  
  <p>Something like</p>
  
  <pre><code>SELECT * FROM TableNamePrefix00001
  UNION ALL
  SELECT * FROM TableNamePrefix00002
  UNION ALL
  SELECT * FROM TableNamePrefix00003
  --And so on for all tables in the list
  </code></pre>
  
  <p>Any help appreciated, thanks.</p>