SQL Server - select column_name from (multiple tables_name stored in a variable)
Assuming your table is "similar" to next one and table names are stored there:

CREATE TABLE [MyTable]
(
    [Type] nvarchar(100),
    [Check_column] nvarchar(10)
);

INSERT INTO [MyTable] ([Type]) VALUES
    ('Starter'), ('Meal'), ('Dessert'), ('Drink');


You can dynamically build and execute UPDATE sentences using a CURSOR.

DECLARE @cmd nvarchar(max);
DECLARE @type nvarchar(10);

DECLARE cur CURSOR FOR
    SELECT [Type] FROM [MyTable];

OPEN cur;

FETCH NEXT FROM cur INTO @Type
WHILE(@@FETCH_STATUS = 0)
BEGIN
        SET @cmd = 'update [MyTable]
            set [Check_column] = 
                case when exists(select 1
                                 from   ' + @Type
                              + ' where left([TVF],4) = ''M006'' or left([RPPS],4) = ''1009'')' 
             + ' then ''FAILED'' else ''OK'' end'             
           + ' where [Tabs] = ''' + @Type + '''';

    EXEC sp_executesql @cmd;

    FETCH NEXT FROM cur INTO @Type
END

CLOSE cur;
DEALLOCATE cur;


db&lt;>fiddle here