在SQL Server中从所有表或所有数据库中搜索关键字

从所有表及所有库中搜索关键字
 

在所有表中搜索关键字

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

 

 从所有库中搜索
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'''
--------------------- 
转载自：
原文：https://blog.youkuaiyun.com/xys_777/article/details/5708217