SEARCH A WORD IN DATABASE TABLES

1)
create  proc usp_search_word_in_wholeDB(@word varchar(50))
as
begin
declare @ret_table table(Tname varchar(200),Cname varchar(100))

declare @i int=1,@cnt int=0;
declare @tname varchar(200),@cname varchar(100)
create table #temp(ID INT identity,Tname varchar(200),Cname varchar(100));

insert into #temp(Tname,Cname)
select table_name,column_name  from INFORMATION_SCHEMA.COLUMNS where DATA_TYPE in ('varchar','nvarchar');

set @cnt=@@ROWCOUNT;

while(@i<=@cnt)
begin
select @tname =tname,@cname=Cname from #temp where ID=@i
insert into @ret_table
exec ('select top 1 ''' + @tname + ''','''+ @cname+''' from ['+@tname + ']
where ['+@cname+'] like ''%'+ @word + '%''')

set @i=@i+1;
end;

select * from @ret_table
end;


exec usp_search_word_in_wholeDB @word = 'ma'

2)
CREATE 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'