Sometimes, you may need to find a value in the characters columns (char, varchar, etc) in all
This is one of the methods to do it
declare @sql varchar(max),@search varchar(100)
set @search='your search string'
@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 '
information_schema.columns as c inner join information_schema.tables as t on c.table_name=t.table_name
t.table_type='BASE TABLE' and data_type like '%char%'
The resultset would have table_name, column_name and column_value
Tags: t-sql, sql_server, search,
Can you please explain the method in detail and what does each variable contain and how the method works.
Superb man, i tried and found the results. good job..
Mevlish Dcosta, just print @sql and see what it generates
Thanks Abhinav :)
Thanks a lot.