How to find a particular text in whole database.

Good evening friends..I have heard this question many times "can we search a particular text in whole database?" There is no direct way of searching a particular text in whole database but we can write a stored procedure which can search a particular text in whole database in all text columns like(varchar, nvarchar,text,ntext,char,nchar).

Here is the code :- Please feel free to ask any question, if you find any difficulty to understand this code.

create procedure usp_find_string(@string as varchar(1000))

as

begin

declare @mincounter as int

declare @maxcounter as int

declare @stmtquery as varchar(1000)

set @stmtquery=''

create table #tmp(tablename varchar(128),columnname varchar(128),rowid int identity)

create table #tablelist(tablename varchar(128),columnname varchar(128))

declare @tmp table(name varchar(128))

declare @tablename as varchar(128)

declare @columnname as varchar(128)

insert into #tmp(tablename,columnname)

select a.name,b.name as columnname from sysobjects a

inner join syscolumns b on a.name=object_name(b.id)

where a.type='u'

and b.xtype in(select xtype from systypes

where name='text' or name='ntext' or name='varchar' or name='nvarchar' or name='char' or name='nchar')

order by a.name

select @maxcounter=max(rowid),@mincounter=min(rowid) from #tmp

while(@mincounter <= @maxcounter )

begin

      select @tablename=tablename, @columnname=columnname from #tmp where rowid=@mincounter

      set @stmtquery ='select top 1  ' + '[' +@columnname+']' + ' from ' + '['+@tablename+']' + ' where ' + '['+@columnname+']' + ' like ' + '''%' + @string + '%'''

      insert into @tmp(name) exec(@stmtquery)

      if @@rowcount >0

      insert into #tablelist values(@tablename,@columnname)

      set @mincounter=@mincounter +1

end

select * from #tablelist

end

I hope you will find this article helpful. :)