搜索字符串在哪张表哪个字段中 

 1 create proc p_search
 2 @str varchar(100)
 3 AS
 4 declare @s varchar(8000)
 5 declare tb cursor local for
 6 select s='if exists(select 1 from ['+b.name+'] where ['+a.name+'] like ''%'+@str+'%'')
 7  select ''所在的表及字段: ['+b.name+'].['+a.name+']'''
 8 from syscolumns a join sysobjects b on a.id=b.id
 9 where b.xtype='U' and a.status>=0
10  and a.xusertype in(175,239,231,167)
11 open tb
12 fetch next from tb into @s
13 while @@fetch_status=0
14 begin
15  exec(@s)
16  fetch next from tb into @s
17 end
18 close tb
19 deallocate tb

 

posted @ 2015-07-28 13:50  Ray's  阅读(130)  评论(0)    收藏  举报