根据字段值查询在那张表那个字段

--exec Full_Search '搜索条件  '

CREATE proc Full_Search(@string varchar(50))
AS
begin

declare @tbname varchar(50)
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(50)
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(1000),@j int
SELECT @sql= 'select @i=count(1) from ' +@tbname + ' where '+ @colname+ ' like '+ '''%'+@string+ '%'''
exec sp_executesql @sql,N'@i int output',@i=@j output --输出满足条件表的记录数
IF @j> 0
BEGIN
SELECT 包含字串的表名=@tbname
--exec( 'select distinct '+@colname+' from ' +@tbname + ' where '+ @colname+ ' like '+ '''%'+@string+ '%''')
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