SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
go
ALTER PROC [dbo].[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