SQL Server 尋找相關資料庫物件：FindObject_usp 與 sp_Occurrences 實用工具

痛點：變更前影響分析耗時費力

修改資料表結構或預存程序前，必須找出所有相依物件（檢視表、函數、觸發器、其他 SP 等）。手工搜尋 syscomments 易漏、跨資料庫更麻煩。本文介紹兩個實用工具預存程序，快速定位相關物件。

工具一：FindObject_usp —— 跨資料庫物件名稱搜尋

參數定義

支援的 @objtype 值對照

使用範例

-- 1. 所有資料庫搜尋含 'bak' 的所有物件
EXEC FindObject_usp '%bak%';

-- 2. 僅搜尋使用者資料表
EXEC FindObject_usp '%bak%', 'User table';
-- 或 EXEC FindObject_usp '%bak%', 'U';

-- 3. 僅搜尋預存程序
EXEC FindObject_usp '%bak%', 'Stored procedure';
-- 或 EXEC FindObject_usp '%bak%', 'P';

適用時機

• 資料表結構變更前（增/刪欄、型別變更）尋找相依 SP/View/Function。
• 重命名物件前確認影響範圍。
• 跨專案資料庫統一命名規範稽核。

工具二：sp_Occurrences —— 可程式物件內文本搜尋

功能差異

• FindObject_usp：搜尋物件名稱（metadata），跨資料庫。
• sp_Occurrences：搜尋物件定義內文本（如 SP 內的 SQL 字串、變數名），僅限當前資料庫。

使用範例

USE TargetDatabase;
GO
EXEC sp_Occurrences 'bak';  -- 找出所有可程式物件定義中出現 'bak' 的位置

擴展跨資料庫搜尋

原版 sp_Occurrences 僅支援單一資料庫。若需跨庫，可參考以下模式自行擴充：

CREATE PROCEDURE dbo.sp_Occurrences_AllDBs @SearchTerm VARCHAR(200)
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX) = N'';
    SELECT @sql = @sql + N'
        USE [' + name + N'];
        EXEC sp_Occurrences @SearchTerm;'
    FROM sys.databases
    WHERE state_desc = 'ONLINE' AND database_id > 4;  -- 排除系統庫
    EXEC sp_executesql @sql, N'@SearchTerm VARCHAR(200)', @SearchTerm;
END

內建替代方案（無需自訂工具）

• SSMS 物件總管搜尋：Ctrl+Shift+D → 物件搜尋 → 輸入關鍵字，支援跨庫、類型過濾（SQL Server 2016+ 內建）。
• sys.sql_modules：SELECT * FROM sys.sql_modules WHERE definition LIKE '%bak%'（單庫）。
• Visual Studio / VS Code SQL Server 擴充：專案層級全文搜尋，支援 Git 歷史比對。
• Redgate SQL Search / ApexSQL Search：免費增效工具，UI 友善、支援正則、匯出報表。

結語

FindObject_usp 與 sp_Occurrences 是經典 DBA 工具箱必備，雖然新版 SSMS 已內建類似功能，但在自動化腳本、CI/CD 管線前置檢查、舊版環境（2005/2008/2012）中仍具不可替代價值。建議部署至 master 或專用工具資料庫，並納入變更管理 SOP。