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. :)

No comments: