SQL Server Search a value in character column of all tables

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 '''+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