萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 尋找資料庫中包含特定值的資料表與欄位

需求情境

已知某個值(如客戶編號「CUST001」),需快速找出它存在於哪個資料表的哪個欄位中。手動逐表查詢極其低效,透過系統目錄與動態 SQL 可自動化完成。

解決方案:搜尋儲存程序

CREATE PROCEDURE [dbo].[SP_FindValueInDB]
    @value VARCHAR(1024)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sql VARCHAR(MAX),
            @table SYSNAME,
            @column SYSNAME;

    -- 暫存結果表
    CREATE TABLE #results (
        tablename SYSNAME,
        columnname SYSNAME
    );

    -- 遊標遍歷所有使用者資料表的字串型欄位
    DECLARE tables_cursor CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
    SELECT o.name, c.name
    FROM sys.columns c
    JOIN sys.objects o ON c.object_id = o.object_id
    WHERE o.type = 'U' 
      AND c.system_type_id IN (
          SELECT system_type_id FROM sys.types 
          WHERE name IN ('varchar','nvarchar','char','nchar','text','ntext')
      )
    ORDER BY o.name, c.column_id;

    OPEN tables_cursor;
    FETCH NEXT FROM tables_cursor INTO @table, @column;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 動態建構查詢:使用 QUOTENAME 防注入
        SET @sql = N'IF EXISTS (SELECT 1 FROM ' + QUOTENAME(@table) + 
                     N' WHERE ' + QUOTENAME(@column) + N' LIKE %' + @value + N'%) ' +
                     N'INSERT #results VALUES (' + QUOTENAME(@table, '''') + N', ' + 
                     QUOTENAME(@column, '''') + N')';
        
        EXEC (@sql);

        FETCH NEXT FROM tables_cursor INTO @table, @column;
    END

    CLOSE tables_cursor;
    DEALLOCATE tables_cursor;

    SELECT * FROM #results;
    DROP TABLE #results;
END

關鍵改進說明

  • 使用 sys.columns + sys.objects 替代舊版 syscolumns/sysobjects
  • 以 system_type_id 精確篩選字串型別,避免隱性轉換錯誤
  • QUOTENAME 防止物件名稱注入
  • CURSOR LOCAL FAST_FORWARD READ_ONLY 提升遊標效能
  • SET NOCOUNT ON 減少網路往返

使用範例

EXEC SP_FindValueInDB 'CUST001';
-- 結果:
-- tablename   columnname
-- Customers   CustomerID
-- Orders      CustomerID

效能考量與優化

  • 大型資料庫建議限制搜尋範圍(指定 Schema、排除大表)
  • 可改用全文檢索或 sys.dm_db_index_physical_stats 針對性掃描
  • 生產環境謹慎使用,建議離峰時段執行

擴展:搜尋數值/日期型欄位

修改 system_type_id 篩選條件即可擴展至 int、datetime 等型別,但需注意格式轉換與比較邏輯。

總結

此程序提供快速定位值分佈的能力,適合資料探勘、除錯與審計場景。生產使用時請加入權限控制與執行時間限制。

copyright © 萬盛學電腦網 all rights reserved