SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE dbo.DeviceLogs_7_2022(id int);
CREATE TABLE dbo.DeviceLogs_8_2022(id int);

INSERT dbo.DeviceLogs_7_2022(id) VALUES(1),(2);
INSERT dbo.DeviceLogs_8_2022(id) VALUES(3),(4);
4 rows affected

DECLARE @sql nvarchar(max);

SELECT @sql = STRING_AGG(CONVERT(nvarchar(max), 
  CONCAT(N'  SELECT * FROM dbo.', QUOTENAME(name))), N'
  UNION ALL
')
FROM sys.tables
WHERE name LIKE N'DeviceLogs_[0-9]%';

SET @sql = N'CREATE OR ALTER VIEW dbo.SomeViewName
AS
' + @sql;

PRINT @sql;
EXEC sys.sp_executesql @sql;
CREATE OR ALTER VIEW dbo.SomeViewName
AS
  SELECT * FROM dbo.[DeviceLogs_7_2022]
  UNION ALL
  SELECT * FROM dbo.[DeviceLogs_8_2022]


SELECT * FROM dbo.SomeViewName;


id


1

2

3

4