SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE TL202001I(i INT);
CREATE TABLE TL202002I(i INT);
CREATE TABLE TL202003I(i INT);

INSERT INTO TL202001I VALUES(1);

INSERT INTO TL202002I VALUES(2);

INSERT INTO TL202003I VALUES(3);
3 rows affected

DECLARE @create_view_sql NVARCHAR(MAX);

SELECT @create_view_sql = N'CREATE OR ALTER VIEW my_combined_view AS ' +  
       STUFF(STRING_AGG(FORMATMESSAGE('UNION ALL SELECT * FROM %s.%s'
     ,QUOTENAME(TABLE_SCHEMA), QUOTENAME(TABLE_NAME))
    , CHAR(13)) WITHIN GROUP(ORDER BY TABLE_NAME), 1,10,'')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'TL2[0-9][0-9][0-9][0-9][0-9]I'
  AND TABLE_TYPE = 'BASE TABLE';
  
PRINT @create_view_sql;

EXEc sp_executesql @create_view_sql;
CREATE OR ALTER VIEW my_combined_view AS SELECT * FROM [dbo].[TL202001I]
UNION ALL SELECT * FROM [dbo].[TL202002I]
UNION ALL SELECT * FROM [dbo].[TL202003I]


SELECT * FROM my_combined_view


i


1

2

3


CREATE TABLE TL202004I(i INT);
INSERT INTO TL202004I VALUES(4);
1 rows affected

DECLARE @create_view_sql NVARCHAR(MAX);

SELECT @create_view_sql = N'CREATE OR ALTER VIEW my_combined_view AS ' +  
       STUFF(STRING_AGG(FORMATMESSAGE('UNION ALL SELECT * FROM %s.%s'
     ,QUOTENAME(TABLE_SCHEMA), QUOTENAME(TABLE_NAME))
    , CHAR(13)) WITHIN GROUP(ORDER BY TABLE_NAME), 1,10,'')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'TL2[0-9][0-9][0-9][0-9][0-9]I'
  AND TABLE_TYPE = 'BASE TABLE';
  
PRINT @create_view_sql;

EXEc sp_executesql @create_view_sql;
CREATE OR ALTER VIEW my_combined_view AS SELECT * FROM [dbo].[TL202001I]
UNION ALL SELECT * FROM [dbo].[TL202002I]
UNION ALL SELECT * FROM [dbo].[TL202003I]
UNION ALL SELECT * FROM [dbo].[TL202004I]


SELECT * FROM my_combined_view


i


1

2

3

4