- 论坛徽章:
- 6
|
declare @str varchar(100)
set @str='a' --要搜索的字符串
declare @s varchar(8000)
declare tb cursor local for
select 'if exists(select 1 from ['+b.name+'] where ['+a.name+'] like ''%'+@str+'%'')
print '' ['+b.name+'].['+a.name+']'''
from syscolumns a join sysobjects b on a.id=b.id
where b.xtype='U' and a.status>=0
and a.xusertype in(175,239,231,167)
open tb
fetch next from tb into @s
while @@fetch_status=0
begin
exec(@s)
fetch next from tb into @s
end
close tb
deallocate tb
-- 2 结合上面 从所有库中搜索
if object_id('up_ValueSearch')is not null drop proc up_ValueSearch
go
create proc up_ValueSearch @db varchar(200),@str varchar(100)
as
begin
declare @s varchar(8000)
declare tb cursor local for
select 'if exists(select 1 from ['+b.name+'] where ['+a.name+'] like ''%'+@str+'%'')
print '' ['+@db+']..['+b.name+'].['+a.name+']'''
from syscolumns a join sysobjects b on a.id=b.id
where b.xtype='U' and a.status>=0
and a.xusertype in(175,239,231,167)
open tb
fetch next from tb into @s
while @@fetch_status=0
begin
exec(@s)
fetch next from tb into @s
end
close tb
deallocate tb
end
go
-- 调用示例
sp_msforeachdb 'up_ValueSearch ?,''aa'''
还有其他的方法,请参考:http://blog.csdn.net/xys_777/archive/2010/07/02/5708217.aspx |
|