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.
I hope you will find this article helpful. :)
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:
Post a Comment