mssql 全库搜索某一个字段的值

最近在对一个系统进行二次开发时,有接近两千个表,没有数据结构,需要找一些值的定义正好用到了下面的存储过程,可以在mssql 数据库中搜索某一个符合条件的字段值. like 匹配,我注释掉了.请自行修改,我目前在ms sqlserver 2008 r2 版本上运行正常.

create procedure Full_Search(@string varchar(100))
as
begin
declare @tbname varchar(100)
declare tbroy cursor for select name from sysobjects
where xtype=’u’ –第一个游标遍历所有的表

open tbroy
fetch next from tbroy into @tbname
while @@fetch_status=0
begin

declare @colname varchar(100)
declare colroy cursor for select name from syscolumns
where id=object_id(@tbname) and xtype in (
select xtype from systypes
where name in (‘varchar’,’nvarchar’,’char’,’nchar’) –数据类型为字符型的字段
) –第二个游标是第一个游标的嵌套游标，遍历某个表的所有字段

open colroy
fetch next from colroy into @colname
while @@fetch_status=0
begin

declare @sql nvarchar(4000),@j int
–select @sql=’select @i=count(1) from ‘ +quotename(@tbname) +’ where ‘+ quotename(@colname)+’ like ‘+”’%’+@string+’%”’
select @sql=’select @i=count(1) from ‘ +quotename(@tbname) +’ where ‘+ quotename(@colname)+’ = ‘+””+@string+””
exec sp_executesql @sql,N’@i int output’,@i=@j output –输出满足条件表的记录数
if @j>0
begin
declare @v varchar(8000)
–set @v=’select distinct ‘+quotename(@colname)+’ from ‘ +quotename(@tbname) +’ where ‘+ quotename(@colname)+’ like ‘+”’%’+@string+’%”’
set @v=’select distinct ‘+quotename(@colname)+’,’+ quotename(@tbname) +’ as tbname from ‘ +quotename(@tbname) +’ where ‘+ quotename(@colname)+’ = ‘+””+@string+””
exec(@v)
end
fetch next from colroy into @colname
end

close colroy
deallocate colroy

fetch next from tbroy into @tbname
end
close tbroy
deallocate tbroy
end
go