遍历数据库

declare @str varchar(100) 
set @str='张三' --要搜索的字符串

declare @s varchar(8000) 
declare tb cursor local for 
select s='if exists(select 1 from ['+b.name+'] where convert(varchar,['+a.name+']) like ''%'+@str+'%'') 
print ''select ['+a.name+'] from ['+b.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,56,60,108,106) 
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



------------------------------------test2---------------------------------------


declare @s varchar(8000) 
declare tb cursor local for 
select 'if exists( select ' +syscolumns.name+' from ' +sysobjects.name + ' where ' +syscolumns.name+ ' = ''此处为查询字段'' ) 
print '' select ' +syscolumns.name+ ' from ' + sysobjects.name+ '''' 
from sysobjects left join syscolumns on sysobjects.id = syscolumns.id
where sysobjects.xtype = 'u' ---------此处中选择了用户表---
and syscolumns.xtype = 36 ----------此处为查询字段的数据类型选择了 select * from sys.systypes where name = 'uniqueidentifier'--
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