SQL Server 全庫全表模糊搜尋：尋找包含指定關鍵字的所有欄位資料

當需要在整個資料庫中尋找包含特定關鍵字的資料，且不確定具體在哪張表、哪個欄位時，手工撰寫查詢極其繁瑣。本文提供一套動態產生 SQL 的方案，自動掃描所有使用者表的字串型欄位，並建立暫存函數批次產生查詢語句，最終一次性輸出所有符合條件的結果。

核心思路

1. 從系統表 syscolumns、sysobjects、systypes 篩選出所有使用者表（xtype='u'）的字串型欄位（varchar、nvarchar、char、nchar、text、ntext）。
2. 將欄位資訊存入暫存表 #t（原文用實體表 t，建議改為暫存表避免殘留）。
3. 建立函數 udf_genSQL，接收表名與關鍵字，動態拼接 WHERE col1 LIKE '%keyword%' OR col2 LIKE '%keyword%' ... 條件。
4. 對每張表呼叫該函數，取得完整查詢語句並執行。

完整腳本（已優化為暫存表版）

-- 1. 收集所有使用者表的字串型欄位
SELECT a.name AS columnname, OBJECT_NAME(a.id) AS tablename
INTO #t
FROM syscolumns a
JOIN sysobjects b ON a.id = b.id
JOIN systypes c ON a.xtype = c.xtype
WHERE b.xtype = 'u'
  AND c.name IN ('varchar','nvarchar','char','nchar','text','ntext')
  AND OBJECT_NAME(a.id) <> 't';

-- 2. 建立動態產生查詢的函數
CREATE FUNCTION dbo.udf_genSQL(@tableName VARCHAR(1000), @keyWord VARCHAR(1000))
RETURNS VARCHAR(8000)
AS
BEGIN
    DECLARE @sql VARCHAR(8000);
    SET @sql = 'SELECT * FROM ' + @tableName + ' WHERE 1=1 ';
    SELECT @sql = @sql + ' OR ' + columnname + ' LIKE ''%' + @keyWord + '%'''
    FROM #t
    WHERE tablename = @tableName;
    RETURN @sql;
END;
GO

-- 3. 逐表執行查詢
SELECT dbo.udf_genSQL(tablename, '目標關鍵字') FROM #t GROUP BY tablename;

-- 4. 清理環境
DROP TABLE #t;
DROP FUNCTION dbo.udf_genSQL;

使用注意事項

• 效能考量：全庫掃描屬重度操作，建議在離峰時段或備份庫上執行。
• 結果集合併：上述腳本輸出的是多條獨立 SELECT 語句，需在應用層或進一步包裝為動態 SQL 批次執行並合併結果。
• 大型欄位：text/ntext 已棄用，新專案請改用 varchar(max)/nvarchar(max)，並相應調整類型篩選條件。

進階替代方案

SQL Server 2016+ 可考慮使用 Full-Text Search 或 Azure Cognitive Search 等專業全文檢索功能，效能與功能性均優於動態 SQL 方案。