SQL- UNION ALL a large number of tables

Question

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.

I will likely merge these tables in the future, should be trivial once the select works.

--Getting a list of all tables 
select TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'TableNamePrefix%'
ORDER BY TABLE_NAME

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.

Something like

SELECT * FROM TableNamePrefix00001
UNION ALL
SELECT * FROM TableNamePrefix00002
UNION ALL
SELECT * FROM TableNamePrefix00003
--And so on for all tables in the list

Any help appreciated, thanks.

Answer 1

You can do this with Dynamic SQL

Declare @SQL varchar(max) =''
Select @SQL = @SQL +'Union All Select * From '+Table_Name+' ' 
  FROM INFORMATION_SCHEMA.TABLES
  WHERE TABLE_NAME LIKE 'TableNamePrefix%'
  ORDER BY TABLE_NAME
Set @SQL = Stuff(@SQL,1,10,'')
Exec(@SQL)

Answer 2

using your pattern on table name - i got somewhere with

DECLARE @SQL nvarchar(max);

select @SQL =  COALESCE(@SQL , '') + 'SELECT * FROM [' +  TABLE_NAME + ']  UNION ALL ' 
FROM INFORMATION_SCHEMA.TABLES where TABLE_NAME LIKE '%employeedet%';

SELECT @SQL = LEFT(@SQL, LEN(@SQL) - 11);

print @SQL;