全站关键字搜索

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