If you want to select records from all tables that exist where the table name is like ‘orders’ you would need to iterate through a list of table names that meet the criteria, pass in the table name as a variable, then run a select statement using the variable as a table name. To do this you would need to use a loop.

declare @name as varchar(128)
DECLARE cur CURSOR
FOR    
select name from sys.tables
where name like '%order%'

OPEN cur FETCH NEXT FROM cur INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN

--I have used a top 5 condition to limit results
exec('select top 5 * from ' + @name)
  
FETCH NEXT FROM cur INTO @name
END
CLOSE cur
DEALLOCATE cur