--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 declare @str varchar(100) set @str='E11' --要搜索的字符串 declare @s varchar(8000) declare tb cursor local for select 'if exists(select 1 from ['+b.name+'] where ['+a.name+'] = '''+@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)--36是guid 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 --搜索数字 declare @str varchar(100) set @str='430000' --要搜索的字符串 declare @s varchar(8000) declare tb cursor local for select 'if exists(select 1 from ['+b.name+'] where cast(['+a.name+'] as nvarchar(100)) 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,106) open tb fetch next from tb into @s while @@fetch_status=0 begin -- print @s; exec(@s) fetch next from tb into @s end close tb deallocate tb --搜索日期 declare @str varchar(100) set @str='2015-05-12' --要搜索的字符串 declare @s varchar(8000) declare tb cursor local for select 'if exists(select 1 from ['+b.name+'] where charindex('''+@str+''',' + 'convert(varchar,['+a.name+'] ,120)' + ')>0) 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 = 61 open tb fetch next from tb into @s while @@fetch_status=0 begin --print @s; exec(@s) fetch next from tb into @s end close tb deallocate tb