SQL Server 跨表跨欄位尋找特定值：游標動態搜尋儲存程序實作

情境

需求：給定一個值（如 'VIP001'），在**資料庫所有使用者表的所有字串型欄位**中搜尋包含該值的資料列，回傳 Schema、Table、Column、符合資料。適用於資料追蹤、異常排查、未知來源值溯源。

實作：系統儲存程序 SP_FindValueInDB

CREATE PROCEDURE dbo.SP_FindValueInDB
    @SearchValue NVARCHAR(1024)
AS
BEGIN
    SET NOCOUNT ON;

    -- 1. 暫存結果表
    CREATE TABLE #Results (
        SchemaName SYSNAME,
        TableName SYSNAME,
        ColumnName SYSNAME,
        MatchValue NVARCHAR(MAX)
    );

    -- 2. 宣告游標走訪所有字串欄位
    DECLARE @SchemaName SYSNAME, @TableName SYSNAME, @ColumnName SYSNAME;
    DECLARE @SQL NVARCHAR(MAX);

    DECLARE Cur CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
    SELECT 
        SCHEMA_NAME(o.schema_id) AS SchemaName,
        o.name AS TableName,
        c.name AS ColumnName
    FROM sys.columns c
    JOIN sys.objects o ON c.object_id = o.object_id
    JOIN sys.types t ON c.user_type_id = t.user_type_id
    WHERE o.type = 'U'                     -- 使用者表
      AND t.name IN (N'char', N'varchar', N'nchar', N'nvarchar', N'text', N'ntext') -- 字串型別
      AND c.max_length > 0;                -- 排除 MAX 型別效能考量（可選）

    OPEN Cur;
    FETCH NEXT FROM Cur INTO @SchemaName, @TableName, @ColumnName;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 3. 動態建構並執行搜尋 SQL
        SET @SQL = N'IF EXISTS (SELECT 1 FROM ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) +
                     N' WHERE ' + QUOTENAME(@ColumnName) + N' LIKE N''%'' + @Val + N''%'') ' +
                     N'INSERT #Results VALUES (@Sch, @Tbl, @Col, @Val);';

        EXEC sp_executesql @SQL,
            N'@Val NVARCHAR(1024), @Sch SYSNAME, @Tbl SYSNAME, @Col SYSNAME, @ValOut NVARCHAR(MAX) OUTPUT',
            @Val = @SearchValue,
            @Sch = @SchemaName,
            @Tbl = @TableName,
            @Col = @ColumnName;

        FETCH NEXT FROM Cur INTO @SchemaName, @TableName, @ColumnName;
    END

    CLOSE Cur;
    DEALLOCATE Cur;

    -- 4. 回傳結果
    SELECT * FROM #Results;
    DROP TABLE #Results;
END
GO

呼叫範例

EXEC dbo.SP_FindValueInDB N'VIP001';

關鍵技術點解析

效能優化建議

1. 全文檢索：若頻繁執行全庫搜尋，請建立全文檢索目錄，使用 CONTAINS/FREETEXT，速度快數量級。
2. 限定 Schema/Table：加入參數 @SchemaFilter、@TableFilter 縮小搜尋範圍。
3. 非生產環境執行：全庫掃描極度消耗資源，**嚴禁在生產高峰期直接執行**；建議備份還原至測試庫跑腳本。
4. 分批次處理：大資料庫可先查出候選表/欄位清單，再分批次搜尋，避免長時間鎖定系統表。

替代方案比較