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