SQL Server 2012 全庫全表模糊搜尋字符串：雙層游標動態 SQL 實戰

需求：在不知表名、列名的情況下，搜尋全庫所有用戶表的所有字符型列，找出包含指定關鍵字的行。適用於數據治理、審計、敏感信息排查。

完整實戰腳本

USE [YourDatabase];
GO

DECLARE @KeyFind NVARCHAR(MAX) = N'123';  -- 搜尋關鍵字

-- 1. 遍歷所有用戶表
DECLARE TableCur CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
    SELECT QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name)
    FROM sys.tables
    WHERE type = 'U' AND name <> 'dtproperties';

DECLARE @TableName NVARCHAR(MAX);
OPEN TableCur;
FETCH NEXT FROM TableCur INTO @TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 2. 遍歷該表所有字符型列
    DECLARE @ColSql NVARCHAR(MAX) = N'
        SELECT QUOTENAME(COLUMN_NAME)
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = PARSENAME(''' + @TableName + ''', 2)
          AND TABLE_NAME = PARSENAME(''' + @TableName + ''', 1)
          AND DATA_TYPE IN (''char'',''varchar'',''nchar'',''nvarchar'',''text'',''ntext'',''xml'')';

    DECLARE ColCur CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
        EXEC sp_executesql @ColSql;  -- 動態執行獲取列名

    DECLARE @ColName NVARCHAR(MAX);
    OPEN ColCur;
    FETCH NEXT FROM ColCur INTO @ColName;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 3. 動態構建並執行模糊查詢
        DECLARE @DynSql NVARCHAR(MAX) = N'
            IF EXISTS (SELECT 1 FROM ' + @TableName + ' WHERE ' + @ColName + N' LIKE N''%' + @KeyFind + N'%'')
            BEGIN
                DECLARE @Cnt BIGINT = (SELECT COUNT(*) FROM ' + @TableName + ');
                PRINT N''Found: Table [' + @TableName + '], Column [' + @ColName + '], Row Count: '' + CAST(@Cnt AS NVARCHAR(20)) + N''.'';
            END';
        
        EXEC sp_executesql @DynSql;

        FETCH NEXT FROM ColCur INTO @ColName;
    END
    CLOSE ColCur;
    DEALLOCATE ColCur;

    FETCH NEXT FROM TableCur INTO @TableName;
END
CLOSE TableCur;
DEALLOCATE TableCur;
GO

關鍵技術點

效能優化建議

1. 並行化：將表列表分批，用 sp_start_job 或 SQL Agent 多作業並行跑。
2. 全文檢索替代：若頻繁搜尋，建立 全文索引，CONTAINS/FREETEXT 秒級返回。
3. 限制範圍：按 Schema/表名前綴過濾，避免掃描海量日志/歷史表。
4. 結果持久化：PRINT 輸出難捕獲，建議 INSERT INTO #ResultLog ... 存表。

安全與合規提醒

• 搜尋敏感數據（身份證、手機號、銀行卡）屬於數據安全審計範疇，需走審批流程，操作留存審計日志。
• 生產庫執行前必須在備份/只讀副本驗證，避免長時間鎖表影響業務。
• 大表字符列無索引時 LIKE '%...%' 會全表掃描，建議維護窗口執行並監控 MAXDOP。

現代替代方案：sys.dm_exec_describe_first_result_set (SQL 2012+)

可一次性獲取所有表列元數據，減少游標層級，但動態構建查詢邏輯本質不變。