SQL- UNION ALL a large number of tables
<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>