Query multiple tables that have date pattern appended to their names

Question

We have a database that creates a new table for each new day. The naming of the tables follows this pattern: History_tbl_[year]_[month]_[day]. A sample name of the tables for the last 5 days for example is:

History_tbl_2021_10_02
History_tbl_2021_10_01
History_tbl_2021_09_30
History_tbl_2021_09_29
History_tbl_2021_09_28

My goal is to be able to query all the tables from a given date range at once. I can manually select the tables with union all, but it takes lots of time especially if I want to do a long date range. Is there a better way to solve this?

Note: Unfortunately, I don't have the privilege to change the structure and make all data being stored in a single table.

Answer 1

One bare-bones method to generate a list of unioned queries and dynamically execute them would be as follows, tweak as necessary:

declare @from date='20211001', @to date='20211004', @sql nvarchar(max)='';

with d as (
    select DateAdd(day, number, @from) dt
    from master..spt_values
    where type = 'P'
    and DateAdd(day, number, @from) <= @to
)
select @sql=String_Agg(qry,' ')
from (
    select 'select col1, col2, col3 from History_tbl_' 
        + Concat(Year(dt),'_',Right(Concat('0',month(dt)),2),'_',  Right(Concat('0',Day(dt)),2)) 
        + Iif( dt=Max(dt) over()  ,'', ' union all')  qry
    from d
) x

exec sp_executesql @sql

Note that the CTE generates the date range on the fly, ideally you would use a permanent calendar table