I use this one (cant remember where I found it though):

CREATE TABLE myTable99 (TABLE_NAME sysname, COLUMN_NAME sysname, Occurs int)

GO

SET NOCOUNT ON

DECLARE @SQL varchar(8000), @TABLE_NAME sysname, @COLUMN_NAME sysname, @Sargable varchar(80), @Count int

SELECT @Sargable = 'enter word or words here'

DECLARE insaneCursor CURSOR FOR

SELECT c.TABLE_NAME, c.COLUMN_NAME

FROM INFORMATION_SCHEMA.Columns c INNER JOIN INFORMATION_SCHEMA.Tables t

ON t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME

WHERE c.DATA_TYPE IN ('char','nchar','varchar','nvarchar','text','ntext')

AND t.TABLE_TYPE = 'BASE TABLE'

OPEN insaneCursor

FETCH NEXT FROM insaneCursor INTO @TABLE_NAME, @COLUMN_NAME

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @SQL = 'INSERT INTO myTable99 (TABLE_NAME, COLUMN_NAME, Occurs) SELECT '

+ '''' + @TABLE_NAME + '''' + ','

+ '''' + @COLUMN_NAME + '''' + ','

+ 'COUNT(*) FROM [' + @TABLE_NAME

+ '] WHERE [' + @COLUMN_NAME + '] Like '

+ ''''+ '%' + @Sargable + '%' + ''''

--SELECT @SQL

EXEC(@SQL)

IF @@ERROR <> 0

BEGIN

SELECT @SQL

SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = @TABLE_NAME

GOTO Error

END

FETCH NEXT FROM insaneCursor INTO @TABLE_NAME, @COLUMN_NAME

END

SELECT * FROM myTable99 WHERE Occurs <> 0

Error:

CLOSE insaneCursor

DEALLOCATE insaneCursor

GO

DROP TABLE myTable99

GO

SET NOCOUNT OFF