SQL Server - View or Stored Proc to Union All On a Growing Table List

Question

The SQL server has tables that all have the same structure. tableX_2016, tableX_2017, tableX_2018, tableX_2019, tableX_2020, tableX_2021.

I want a view that is simply:

select * from tableX_2016 
union all select * from tableX_2017
union all select * from tableX_2018
union all select * from tableX_2019
union all select * from tableX_2020
union all select * from tableX_2021

However, I would like that view to automatically pick up tableX_2022, tableX_2023, etc., once they exist. The best would be to put all this data in one table (!), but unfortunately, I do not have the latitude to do that.

Is there a way to do this in a view? Or, a way to do it in a stored procedure and then somehow consume that stored procedure as if it were a view (use its output in CTEs, join to its output, etc)?

Thanks!

Answer 1

Database design seems poor. If you are keeping data in same database, it would be wise to handle by creating year or date column.

However, you can achieve your result by the given query

    DROP TABLE IF EXISTS ##temp
    SELECT * INTO ##temp FROM dbo.tableX_2016
    TRUNCATE TABLE ##temp
    
    EXEC sp_MSforeachtable
    @command1 = 'INSERT INTO ##temp SELECT * FROM ?',
    @whereand = 'AND OBJECT_NAME(OBJECT_ID) LIKE ''tableX_%'''
    
    SELECT * FROM ##temp

Note that, sp_MSforeachtable is an undocumented stored procedure in SQL Server

PS: You can create view, stored procedure, etc. as per the need using above query