已知某個值(如客戶編號「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/sysobjectssystem_type_id 精確篩選字串型別,避免隱性轉換錯誤QUOTENAME 防止物件名稱注入CURSOR LOCAL FAST_FORWARD READ_ONLY 提升遊標效能SET NOCOUNT ON 減少網路往返EXEC SP_FindValueInDB 'CUST001';
-- 結果:
-- tablename columnname
-- Customers CustomerID
-- Orders CustomerID
sys.dm_db_index_physical_stats 針對性掃描修改 system_type_id 篩選條件即可擴展至 int、datetime 等型別,但需注意格式轉換與比較邏輯。
此程序提供快速定位值分佈的能力,適合資料探勘、除錯與審計場景。生產使用時請加入權限控制與執行時間限制。