🌏 Read the English version

SQL Server 全資料庫文字搜尋：完整實作指南與效能優化

引言

在管理大型 SQL Server 資料庫時，經常會遇到需要在整個資料庫中搜尋特定文字的需求。無論是追蹤敏感資料、除錯問題資料，或是進行資料遷移，全資料庫搜尋都是資料庫管理員與開發人員的重要工具。

本文將深入探討如何在 SQL Server 中實作高效的全資料庫文字搜尋，包含完整程式碼、效能優化技巧、實際應用場景，以及常見問題的解決方案。

第一部分：基礎實作

1. 核心搜尋程式碼

以下程式碼可以在資料庫的所有表格與欄位中搜尋指定文字：

-- 宣告搜尋目標
DECLARE @SearchStr NVARCHAR(100) = N'search_term'

-- 建立結果暫存表
CREATE TABLE #Results (
    ColumnName NVARCHAR(370), 
    ColumnValue NVARCHAR(3630)
)

SET NOCOUNT ON

-- 宣告工作變數
DECLARE @TableName NVARCHAR(256)
DECLARE @ColumnName NVARCHAR(128)
DECLARE @SearchStr2 NVARCHAR(110)

SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%', '''')

-- 迴圈處理每個表格
WHILE @TableName IS NOT NULL
BEGIN
    SET @ColumnName = ''
    
    -- 取得下一個表格名稱
    SET @TableName = (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_TYPE = 'BASE TABLE'
            AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            AND OBJECTPROPERTY(
                OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 
                'IsMSShipped'
            ) = 0
    )
    
    -- 迴圈處理每個欄位
    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
    BEGIN
        -- 取得下一個文字類型欄位
        SET @ColumnName = (
            SELECT MIN(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')
                AND QUOTENAME(COLUMN_NAME) > @ColumnName
        )
        
        -- 執行搜尋並儲存結果
        IF @ColumnName IS NOT NULL
        BEGIN
            INSERT INTO #Results
            EXEC (
                'SELECT ''' + @TableName + '.' + @ColumnName + ''', 
                        LEFT(' + @ColumnName + ', 3630)
                 FROM ' + @TableName + ' (NOLOCK)
                 WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
        END
    END
END

-- 顯示搜尋結果
SELECT ColumnName, ColumnValue 
FROM #Results
ORDER BY ColumnName

-- 清理暫存表
DROP TABLE #Results

2. 程式碼說明

關鍵元件：

• INFORMATION_SCHEMA.TABLES：系統視圖，包含資料庫所有表格資訊
• INFORMATION_SCHEMA.COLUMNS：系統視圖，包含表格欄位資訊
• QUOTENAME()：將名稱加上方括號，避免特殊字元或保留字問題
• PARSENAME()：解析完整物件名稱（Schema.Table）
• NOLOCK：讀取時不鎖定資料表，避免影響生產環境效能
• 動態 SQL (EXEC)：動態組合 SQL 語句執行搜尋

3. 支援的資料類型

預設程式碼支援以下文字類型：

資料類型	說明	最大長度
CHAR	固定長度字串	8,000 bytes
VARCHAR	變動長度字串	8,000 bytes
NCHAR	固定長度 Unicode 字串	4,000 characters
NVARCHAR	變動長度 Unicode 字串	4,000 characters

擴充至其他類型：

-- 支援 TEXT 與 NTEXT（已過時，但舊系統可能使用）
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'text', 'ntext')

-- 支援 MAX 類型
AND (DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
     OR (DATA_TYPE = 'varchar' AND CHARACTER_MAXIMUM_LENGTH = -1)
     OR (DATA_TYPE = 'nvarchar' AND CHARACTER_MAXIMUM_LENGTH = -1))

第二部分：效能優化策略

1. 進階版本：加入進度顯示與中斷機制

-- 進階搜尋程式碼（含進度顯示）
DECLARE @SearchStr NVARCHAR(100) = N'search_term'
DECLARE @TableName NVARCHAR(256)
DECLARE @ColumnName NVARCHAR(128)
DECLARE @SearchStr2 NVARCHAR(110)
DECLARE @TotalTables INT
DECLARE @ProcessedTables INT = 0
DECLARE @StartTime DATETIME = GETDATE()

-- 計算總表格數
SELECT @TotalTables = COUNT(*)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
    AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0

-- 建立結果表（加入更多資訊）
CREATE TABLE #Results (
    TableName NVARCHAR(370),
    ColumnName NVARCHAR(128),
    ColumnValue NVARCHAR(3630),
    RowNumber INT,
    FoundTime DATETIME DEFAULT GETDATE()
)

SET NOCOUNT ON
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%', '''')

WHILE @TableName IS NOT NULL
BEGIN
    SET @ColumnName = ''
    SET @ProcessedTables = @ProcessedTables + 1
    
    -- 顯示進度
    IF @ProcessedTables % 10 = 0
    BEGIN
        DECLARE @ElapsedSeconds INT = DATEDIFF(SECOND, @StartTime, GETDATE())
        DECLARE @EstimatedRemaining INT = (@TotalTables - @ProcessedTables) * @ElapsedSeconds / @ProcessedTables
        
        PRINT 'Progress: ' + CAST(@ProcessedTables AS VARCHAR) + '/' + CAST(@TotalTables AS VARCHAR) + 
              ' (' + CAST(@ProcessedTables * 100 / @TotalTables AS VARCHAR) + '%)' +
              ' - Estimated remaining: ' + CAST(@EstimatedRemaining / 60 AS VARCHAR) + ' minutes'
    END
    
    SET @TableName = (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_TYPE = 'BASE TABLE'
            AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
    )
    
    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
    BEGIN
        SET @ColumnName = (
            SELECT MIN(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')
                AND QUOTENAME(COLUMN_NAME) > @ColumnName
        )
        
        IF @ColumnName IS NOT NULL
        BEGIN
            INSERT INTO #Results (TableName, ColumnName, ColumnValue, RowNumber)
            EXEC (
                'SELECT ''' + @TableName + ''', 
                        ''' + @ColumnName + ''',
                        LEFT(' + @ColumnName + ', 3630),
                        ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
                 FROM ' + @TableName + ' (NOLOCK)
                 WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
        END
    END
END

-- 顯示結果統計
SELECT 
    COUNT(*) AS TotalMatches,
    COUNT(DISTINCT TableName) AS AffectedTables,
    MIN(FoundTime) AS FirstMatch,
    MAX(FoundTime) AS LastMatch
FROM #Results

-- 顯示詳細結果
SELECT 
    TableName,
    ColumnName,
    ColumnValue,
    RowNumber,
    FoundTime
FROM #Results
ORDER BY TableName, ColumnName, RowNumber

DROP TABLE #Results

2. 效能優化技巧

策略一：限制搜尋範圍

-- 只搜尋特定 Schema
AND TABLE_SCHEMA IN ('dbo', 'sales', 'hr')

-- 排除特定表格
AND TABLE_NAME NOT IN ('AuditLog', 'SystemLog', 'HistoryArchive')

-- 只搜尋最近修改的表格
AND OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) IN (
    SELECT object_id 
    FROM sys.tables 
    WHERE modify_date > DATEADD(DAY, -30, GETDATE())
)

策略二：平行處理（需要 SQL Server Enterprise Edition）

-- 啟用平行處理
SET @ColumnName = (
    SELECT MIN(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')
        AND QUOTENAME(COLUMN_NAME) > @ColumnName
)
OPTION (MAXDOP 4)  -- 使用 4 個處理器

策略三：使用 Full-Text Index（適合頻繁搜尋）

-- 建立全文檢索索引
CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;

CREATE FULLTEXT INDEX ON dbo.YourTable(YourColumn)
KEY INDEX PK_YourTable
WITH STOPLIST = SYSTEM;

-- 使用全文檢索搜尋
SELECT * FROM dbo.YourTable
WHERE CONTAINS(YourColumn, 'search_term');

第三部分：實際應用場景

場景 1：資料隱私合規檢查

需求：確保資料庫中沒有儲存敏感個資（如身分證字號、信用卡號）。

-- 搜尋台灣身分證格式 (A123456789)
DECLARE @SearchPattern NVARCHAR(100) = N'[A-Z][1-2][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

-- 使用 LIKE 搜尋（簡化版）
WHERE ColumnValue LIKE N'%[A-Z][1-2]%'

-- 搜尋信用卡號格式 (16 位數字)
WHERE ColumnValue LIKE N'%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'

場景 2：資料遷移前的影響評估

需求：更改系統代碼前，找出所有引用舊代碼的資料。

-- 範例：尋找舊產品代碼
DECLARE @OldProductCode NVARCHAR(50) = N'PROD-OLD-001'

-- 執行搜尋
-- ... (使用前述程式碼)

-- 產生更新腳本
SELECT 
    'UPDATE ' + TableName + 
    ' SET ' + ColumnName + ' = REPLACE(' + ColumnName + ', ''' + @OldProductCode + ''', ''PROD-NEW-001'')' +
    ' WHERE ' + ColumnName + ' LIKE ''%' + @OldProductCode + '%''' AS UpdateScript
FROM #Results

場景 3：除錯與錯誤追蹤

需求：系統出現特定錯誤訊息，追蹤錯誤來源。

-- 搜尋錯誤訊息
DECLARE @ErrorMessage NVARCHAR(100) = N'Timeout expired'

-- 找出包含此錯誤的所有紀錄
-- 並顯示完整資料列資訊

SELECT 
    r.TableName,
    r.ColumnName,
    r.ColumnValue,
    -- 取得完整資料列（動態 SQL）
    'SELECT * FROM ' + r.TableName + 
    ' WHERE ' + r.ColumnName + ' = ''' + r.ColumnValue + '''' AS RetrieveFullRow
FROM #Results r

第四部分：最佳實踐與注意事項

1. 生產環境使用建議

注意事項	建議作法
執行時機	在離峰時段執行，避免影響業務運作
權限控制	限制只有 DBA 或特定角色可執行
結果備份	將結果存入永久表格，避免暫存表遺失
執行紀錄	記錄執行時間、搜尋條件、結果數量
資源限制	使用 Resource Governor 限制 CPU 與記憶體使用

2. 安全性考量

-- 建立專用角色
CREATE ROLE FullTextSearchRole;

-- 授予最小必要權限
GRANT VIEW DEFINITION TO FullTextSearchRole;
GRANT SELECT ON INFORMATION_SCHEMA.TABLES TO FullTextSearchRole;
GRANT SELECT ON INFORMATION_SCHEMA.COLUMNS TO FullTextSearchRole;

-- 將使用者加入角色
ALTER ROLE FullTextSearchRole ADD MEMBER [DomainUser];

3. 效能監控

-- 監控執行統計
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- 查看執行計畫
SET SHOWPLAN_ALL ON;

-- 執行搜尋
-- ... (搜尋程式碼)

SET SHOWPLAN_ALL OFF;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;

第五部分：替代方案比較

方案比較表

方案	優點	缺點	適用場景
動態 SQL 搜尋	靈活、不需額外設定	效能較差、資源消耗高	偶爾使用、小型資料庫
Full-Text Index	效能優異、功能強大	需建立索引、維護成本	頻繁搜尋、大型資料庫
第三方工具	UI 友善、功能完整	額外成本、依賴性	企業環境、需要報表
SSMS Object Explorer	簡單直覺、內建工具	功能有限、無法批次	快速查詢、開發環境

使用 SSMS 內建搜尋

SQL Server Management Studio 提供內建的搜尋功能：

1. 在 Object Explorer 中右鍵點選資料庫
2. 選擇「搜尋」→「搜尋資料」
3. 輸入搜尋條件
4. 選擇要搜尋的物件類型

限制：只能搜尋預存程序、函數、檢視等物件定義，無法搜尋表格資料。

常見問題

Q1: 為什麼搜尋非常慢？

A: 全資料庫搜尋是資源密集操作，主要原因包括：

• 表格掃描：需要逐一掃描所有表格的所有列
• LIKE 運算：使用 %keyword% 無法使用索引
• 資料量龐大：大型資料庫可能需要數小時完成

解決方案：

• 限制搜尋範圍（特定 Schema 或表格）
• 在離峰時段執行
• 考慮使用 Full-Text Index
• 增加暫存表的記憶體配置

Q2: 如何搜尋 XML 或 JSON 欄位？

A: 需要特殊處理：

-- 搜尋 XML
WHERE CAST(XmlColumn AS NVARCHAR(MAX)) LIKE '%search_term%'

-- 搜尋 JSON
WHERE JSON_VALUE(JsonColumn, '$.propertyName') LIKE '%search_term%'
OR CAST(JsonColumn AS NVARCHAR(MAX)) LIKE '%search_term%'

Q3: 可以在多個資料庫中搜尋嗎？

A: 可以，使用 sp_MSforeachdb：

CREATE TABLE ##GlobalResults (
    DatabaseName NVARCHAR(128),
    TableName NVARCHAR(370),
    ColumnName NVARCHAR(128),
    ColumnValue NVARCHAR(3630)
)

EXEC sp_MSforeachdb '
USE [?]
IF ''?'' NOT IN (''master'', ''tempdb'', ''model'', ''msdb'')
BEGIN
    -- 在此插入搜尋程式碼
    -- 結果存入 ##GlobalResults
END
'

SELECT * FROM ##GlobalResults
DROP TABLE ##GlobalResults

Q4: 如何避免搜尋系統表格？

A: 使用 OBJECTPROPERTY 過濾：

WHERE OBJECTPROPERTY(
    OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 
    'IsMSShipped'
) = 0  -- 0 表示非系統物件

Q5: 搜尋時如何處理大型 VARCHAR(MAX) 欄位？

A: 需要特殊處理以避免截斷：

-- 修改欄位類型檢查
AND (DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
     OR CHARACTER_MAXIMUM_LENGTH = -1)  -- -1 表示 MAX 類型

-- 修改結果儲存（使用 VARCHAR(MAX)）
CREATE TABLE #Results (
    ColumnName NVARCHAR(370), 
    ColumnValue VARCHAR(MAX)  -- 改用 MAX
)

Q6: 如何限制執行時間避免長時間鎖定？

A: 使用查詢逾時設定：

-- 設定查詢逾時（秒）
SET QUERY_GOVERNOR_COST_LIMIT 300  -- 5 分鐘

-- 或在應用程式層級設定 CommandTimeout
-- sqlCommand.CommandTimeout = 300;

Q7: 搜尋結果太多時如何處理？

A: 加入 TOP 限制：

INSERT INTO #Results
EXEC (
    'SELECT TOP 100 ''' + @TableName + '.' + @ColumnName + ''', 
            LEFT(' + @ColumnName + ', 3630)
     FROM ' + @TableName + ' (NOLOCK)
     WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)

結論

SQL Server 全資料庫文字搜尋是強大但資源密集的操作。透過本文提供的完整程式碼、效能優化策略，以及實際應用案例，你可以：

• 快速實作：使用基礎程式碼立即開始搜尋
• 效能優化：根據資料庫規模選擇適當策略
• 實務應用：解決資料隱私、遷移、除錯等實際問題
• 安全合規：遵循最佳實踐，確保生產環境穩定

關鍵建議：

1. 在生產環境使用前，務必在測試環境驗證效能
2. 考慮建立 Full-Text Index 以提升頻繁搜尋的效能
3. 記錄所有搜尋活動以符合稽核要求
4. 定期檢視與優化搜尋策略

希望本文能幫助你更有效地管理 SQL Server 資料庫。如有任何問題或需要進一步協助，歡迎留言討論。

相關文章

• Azure SQL 搬遷後效能優化實戰：查詢統計、top SQL 分析與索引調整全攻略
• AWS ALB 多實例部署策略：大型專案架構的雙面刃
• AWS CloudFront 8TB 傳輸量分析：如何最佳化費用與效能？
• 系統性能基本概念：平均每秒處理的用戶數量 vs. 總每秒處理的用戶數量
• 深入了解 Redis：資料結構與 CRUD 操作完整指南