DECLARE @SearchText NVARCHAR(100) = '@gmail.com';
DECLARE @SQL NVARCHAR(MAX) = '';

IF OBJECT_ID('tempdb..#Results') IS NOT NULL
    DROP TABLE #Results;

CREATE TABLE #Results
(
    TableName NVARCHAR(256),
    ColumnName NVARCHAR(256),
    MatchingValue NVARCHAR(MAX)
);

SELECT @SQL = @SQL + '
BEGIN TRY
    INSERT INTO #Results (TableName, ColumnName, MatchingValue)
    SELECT
        ''' + s.name + '.' + t.name + ''',
        ''' + c.name + ''',
        CAST([' + c.name + '] AS NVARCHAR(MAX))
    FROM [' + s.name + '].[' + t.name + ']
    WHERE CAST([' + c.name + '] AS NVARCHAR(MAX)) LIKE ''%' + @SearchText + '%''
END TRY
BEGIN CATCH
END CATCH;
'
FROM sys.tables t
INNER JOIN sys.schemas s
    ON t.schema_id = s.schema_id
INNER JOIN sys.columns c
    ON t.object_id = c.object_id
INNER JOIN sys.types ty
    ON c.user_type_id = ty.user_type_id
WHERE ty.name IN ('varchar','nvarchar','char','nchar','text','ntext');

EXEC sp_executesql @SQL;

SELECT *
FROM #Results
ORDER BY TableName, ColumnName;

DROP TABLE #Results;