Sometimes, you may need to find a value in the characters columns (char, varchar, etc) in all 
the tables

This is one of the methods to do it

declare @sql varchar(max),@search varchar(100)

set @sql=''

set @search='your search string'

select 
        @sql=@sql+'select distinct ''['+c.table_name+']'' as table_name,'''+c.column_name+''' as column_name,['+c.column_name+'] as column_value from ['+c.table_name+'] where ['+c.column_name+'] like N''%'+@search+'%'' union all ' 
from 
        information_schema.columns as c inner join information_schema.tables as t on c.table_name=t.table_name 
where 
        t.table_type='BASE TABLE' and data_type like '%char%' 

set @sql=left(@sql,len(@sql)-10)

exec(@sql) 

The resultset would have table_name, column_name and column_value