SQL Server 2017

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

DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

SET @SQL = STUFF((SELECT @CRLF +
                         N'SELECT *' + @CRLF +
                         N'FROM ' + QUOTENAME(s.[name]) + N'.' + QUOTENAME(t.[name]) + @CRLF +
                         N'WHERE ' + 
                         STUFF((SELECT @CRLF +
                                       N'   OR ' + QUOTENAME(c.[name]) + N' LIKE ''%@mail-domain-of-concern.com%'' OR ' + QUOTENAME(c.[name]) + N' LIKE ''%mailto:%'''
                                FROM sys.columns c
                                WHERE c.object_id = t.object_id
                                  AND c.[name] LIKE '%content%'
                                  AND (c.max_length = -1 OR c.max_length > 100)
                                FOR XML PATH(N''),TYPE).value('.','nvarchar(MAX)'),1,8,N'') + N';'
                  FROM sys.tables t 
                       JOIN sys.schemas s ON t.schema_id = s.schema_id
                  WHERE EXISTS(SELECT 1
                               FROM sys.columns c
                               WHERE c.object_id = t.object_id
                                 AND c.[name] LIKE '%content%'
                                 AND (c.max_length = -1 OR c.max_length > 100))
                  FOR XML PATH(N''),TYPE).value(N'.','nvarchar(MAX)'),1,2,N'')

--PRINT @SQL; --Your best friend

EXEC sp_executesql @SQL;

EmailContent	HeaderContent

HTMLContent