SEARCHING STRING IN TABLE

-----SEARCH LETTERS AND WORDS(COLUMN_NAME) IN TOTAL DATABASE


ALTER proc usp_search_word(@word varchar(100)) as
begin
declare @result table(column_name varchar(100),table_name varchar(100))
declare @tname varchar(100),@cname varchar(100)
select TABLE_NAME,column_name into #a from INFORMATION_SCHEMA.COLUMNS 
where DATA_TYPE like '%char%'
declare @str varchar(max)
declare c1 cursor for SELECT COLUMN_NAME,TABLE_NAME from #a
open c1
fetch next from c1 into @cname,@tname
while(@@FETCH_STATUS=0)
begin
set @str='select '''+ @cname + ''','''+ @tname +''' from ' +@tname + ' where '+ @cname
+ ' like ''%' + @word + '%'''
insert into @result
exec( @str)
fetch next from c1 into @cname,@tname
end
close c1
deallocate c1
select * from @result
end

exec usp_search_word 'a'

select top 1 'name','tbl_emp1' from tbl_emp1 where name like '%BABJI%'


=============================================================================

create function fn_get_cols(@table_name varchar(100)) returns  varchar(max)
as
begin
declare @str varchar(max)=''
select @str=@str+','+column_name from INFORMATION_SCHEMA.COLUMNS 
where TABLE_NAME=@table_name and DATA_TYPE like '%char%'
return @str
end