declare @Table table(Id int identity, Name varchar(100));
insert @Table(Name)
(select t.TABLE_NAME from INFORMATION_SCHEMA.TABLES t
join INFORMATION_SCHEMA.COLUMNS c on c.TABLE_NAME = t.TABLE_NAME
where c.COLUMN_NAME = 'Header');
declare @Index int =0;
while(1 =1 )
begin
set @Index += 1;
declare @TableName varchar(100) = (select Name from @Table where id = @Index);
if @TableName is null
break;
exec('select * from ' + @TableName + ' where Header = ''CM resigns today''');
end
create a table variable, it load table name which have column 'Header'. Iterate the table variable and execute dynamic sql where Header column contain 'CM resigns today'.