如何在SQL Server全库检索关键词并返回匹配行的所有列数据

核心思路 #

通过动态SQL遍历数据库中所有用户表，对每张表的所有列做类型兼容转换后匹配目标关键词，匹配成功的表直接返回整张表的所有匹配行，自动按表分组输出结果。

完整存储过程代码 #

CREATE PROCEDURE FullDBSearch_ReturnFullRows
    @SearchKeyword NVARCHAR(1000)
AS
BEGIN
    SET NOCOUNT ON;
    -- 定义遍历用变量
    DECLARE @TableName NVARCHAR(256), @ColumnName NVARCHAR(256), @SQL NVARCHAR(MAX), @WhereClause NVARCHAR(MAX)
    -- 声明游标遍历所有用户表
    DECLARE TableCursor CURSOR FOR
    SELECT QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = 'BASE TABLE'
    ORDER BY TABLE_NAME
    OPEN TableCursor
    FETCH NEXT FROM TableCursor INTO @TableName
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @WhereClause = ''
        -- 遍历当前表的所有列，构造匹配条件
        DECLARE ColumnCursor CURSOR FOR
        SELECT QUOTENAME(COLUMN_NAME)
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = PARSENAME(@TableName, 1)
          AND TABLE_SCHEMA = PARSENAME(@TableName, 2)
          -- 可根据需要排除不需要检索的列，比如大文本、二进制列
          AND DATA_TYPE NOT IN ('image', 'binary', 'varbinary', 'text', 'ntext')
        OPEN ColumnCursor
        FETCH NEXT FROM ColumnCursor INTO @ColumnName
        WHILE @@FETCH_STATUS = 0
        BEGIN
            -- 所有列转成NVARCHAR做兼容匹配，适配字符串、数字、日期等所有类型
            SET @WhereClause = @WhereClause + 'CONVERT(NVARCHAR(MAX), ISNULL(' + @ColumnName + ', '''')) LIKE N''%' + REPLACE(@SearchKeyword, '''', '''''') + '%'' OR '
            FETCH NEXT FROM ColumnCursor INTO @ColumnName
        END
        CLOSE ColumnCursor
        DEALLOCATE ColumnCursor
        -- 去掉末尾多余的OR
        IF LEN(@WhereClause) > 0
        BEGIN
            SET @WhereClause = LEFT(@WhereClause, LEN(@WhereClause) - 3)
            -- 构造查询语句，先输出表名提示，再输出匹配行
            SET @SQL = '
            PRINT N''匹配表：' + @TableName + ''
            SELECT * FROM ' + @TableName + ' WHERE ' + @WhereClause + '
            PRINT N'''' -- 输出空行分隔不同表的结果
            '
            -- 执行动态查询
            EXEC sp_executesql @SQL
        END
        FETCH NEXT FROM TableCursor INTO @TableName
    END
    CLOSE TableCursor
    DEALLOCATE TableCursor
END
GO

使用方法 #

直接调用存储过程传入要检索的关键词即可：

EXEC FullDBSearch_ReturnFullRows @SearchKeyword = N'你要检索的关键词'

注意事项 #

• 若要排除特定表或列，可在游标的查询条件里添加过滤规则，减少不必要的检索开销
• 二进制、大文本类型默认不参与检索，如有需要可自行删除DATA_TYPE的过滤条件
• 日期类关键词检索时需保证输入格式和数据库默认日期格式一致，也可修改存储过程中CONVERT函数的格式参数统一适配
• 30张表的规模下该存储过程运行效率足够，无需额外优化

内容的提问来源于stack exchange，提问作者kushaalk