This is an answer on thread posted by : varad01 .
http://dbforums.com/t401622.html
Some error message was while replying directly on the thread , so, please excuse me for taking additional space for the same topic .

>>>>>>>>>>>>>>

Create Procedure SP_FindString @str_name varchar(50),@Equal bit = 1
AS

SET NOCOUNT ON
SET ANSI_WARNINGS ON
SET ANSI_NULLS ON

Create table #FoundString
(
fs_tname varchar(50),
fs_cname varchar(50)
)

Select substring(a.name,1,50) ATC_tname ,
substring(b.name,1,50) ATC_cname
into #All_TablesColumns
from sysobjects a
inner join syscolumns b on a.id = b.id
inner join systypes st on b.type = st.type
where a.type = 'U' and a.status > 0 and st.name in ('char','varchar')

Declare @tname varchar(100)
Declare @cname varchar(100)
Declare @qry varchar(1000)

Declare All_TablesColumns cursor FOR
Select ATC_tname , ATC_cname from #All_TablesColumns
OPEN All_TablesColumns
FETCH NEXT FROM All_TablesColumns INTO @tname, @cname
WHILE @@FETCH_STATUS = 0

BEGIN
If @Equal = 1
Set @qry = 'select ''' + @tname + ''',''' + @cname + ''' from '+ @tname + ' where ' + @cname + ' = ''' + @str_name + ''''
Else
Set @qry = 'select ''' + @tname + ''',''' + @cname + ''' from '+ @tname + ' where ' + @cname + ' like ''' + @str_name + '%'''
Insert Into #FoundString Exec (@qry)

FETCH NEXT FROM All_TablesColumns INTO @tname, @cname
End

Close All_TablesColumns
Deallocate All_TablesColumns

SELECT DISTINCT * FROM #FoundString

Drop table #All_TablesColumns
Drop table #FoundString

>>>>>>>>>>>>

rgds.
srdjan