DROP TABLE #tbTempTable
CREATE TABLE #tbTempTable (TableName NVARCHAR(1000), TheCount INT)

Declare MyCursor CURSOR
GLOBAL
SCROLL
KEYSET
FOR
SELECT SO.[name] AS TABLENAME, SC.[name] AS COLUMNNAME
FROM sysobjects SO
LEFT JOIN syscolumns SC ON SO.id=SC.id
WHERE SO.xtype = 'U' 
ORDER BY SO.[name], SC.[name]

DECLARE @TableName nvarchar(100)
DECLARE @ColumnName nvarchar(100)
DECLARE @SQLString nvarchar(1000)
DECLARE @INITCount int

OPEN MyCursor

Fetch next from MyCursor INTO @TableName, @ColumnName

while @@fetch_status=0
Begin
--    Set @SQLString = 'SELECT ''' + @TableName + ''', Count(*) FROM ' + @TableName + ' WHERE ' +  @ColumnName + ' LIKE ''%Tester%'''
    Set @SQLString = 'INSERT INTO #tbTempTable (TableName, TheCount) SELECT ''' + @TableName + ''' AS TableName, Count(*) AS TheCount FROM ' + @TableName
    EXEC sp_executesql @SQLString
    Fetch next from MyCursor INTO @TableName, @ColumnName
End

close MyCursor
deallocate MyCursor

SELECT * FROM #tbTempTable WHERE TheCount > 0
/*
OR MAYBE:
SELECT DISTINCT * FROM #tbTempTable WHERE TheCount > 0
*/