sp_stored_procedures + sp_helptext：秒級找回遺忘存儲過程並閱讀源碼

痛點場景

數百個存儲過程、命名不規範、文檔缺失、原作者離職。需要修改三個月前寫的某報表過程，但只記得大致功能關鍵字，無法精確定位名稱。

兩步定位法

步驟 1：列出所有存儲過程 (支持過濾)

-- 1.1 當前數據庫所有用戶存儲過程
EXEC sp_stored_procedures;
-- 返回列：PROCEDURE_QUALIFIER, PROCEDURE_OWNER, PROCEDURE_NAME, NUM_INPUT_PARAMS, NUM_OUTPUT_PARAMS, NUM_RESULT_SETS, REMARKS, PROCEDURE_TYPE

-- 1.2 模糊匹配名稱 (如包含 'report' 'sales' 'monthly')
EXEC sp_stored_procedures @sp_name = '%report%';  -- 支持通配符
-- 或查詢系統視圖更靈活
SELECT name, create_date, modify_date, type_desc
FROM sys.procedures
WHERE name LIKE '%report%' OR OBJECT_DEFINITION(object_id) LIKE '%SalesAmount%';

步驟 2：查看源碼確認邏輯

-- 2.1 顯示創建腳本 (結果以多行文本返回，SSMS 結果窗口可複製)
EXEC sp_helptext 'dbo.usp_MonthlySalesReport';

-- 2.2 程序化獲取 (用於自動化文檔生成/搜索)
DECLARE @Source NVARCHAR(MAX);
SELECT @Source = OBJECT_DEFINITION(OBJECT_ID('dbo.usp_MonthlySalesReport'));
PRINT @Source;  -- 或寫入表/文件

進階：全庫關鍵字全文搜索存儲過程體

-- 搜索所有存儲過程中包含特定表/列/邏輯的對象
SELECT p.name AS ProcName,
       m.definition AS SourceText
FROM sys.procedures p
JOIN sys.sql_modules m ON p.object_id = m.object_id
WHERE m.definition LIKE '%CustomerTable%'  -- 關鍵字
   OR m.definition LIKE '%DiscountCalc%';
-- 大庫建議建立全文索引於 sys.sql_modules.definition 列 (需 CLR/外部引擎)

輔助：獲取參數列表與依賴關係

-- 參數信息
EXEC sp_procedure_params_rowset @procedure_name = N'usp_MonthlySalesReport';
-- 或
SELECT name, type_name(user_type_id) AS DataType, max_length, is_output, has_default_value
FROM sys.parameters
WHERE object_id = OBJECT_ID('dbo.usp_MonthlySalesReport');

-- 依賴對象 (引用的表/視圖/函數)
SELECT referenced_entity_name, referenced_class_desc
FROM sys.sql_expression_dependencies
WHERE referencing_id = OBJECT_ID('dbo.usp_MonthlySalesReport');

最佳實踐：建立元數據檢索儀表盤

1. 每日 Agent 作業掃描 `sys.procedures`、`sys.sql_modules`，提取名稱、創建/修改時間、行數、參數數、引用表、關鍵字標籤 (正則提取) 存入 `DBA_Meta.ProcCatalog` 表。
2. 前端提供關鍵字搜索、標籤篩選、源碼預覽、版本對比 (保留歷史定義)。
3. 集成到 CI/CD：發布前自動掃描 `TODO`/`FIXME`/`HACK` 註釋、禁止 `SELECT *`/`NOLOCK`/游標等反模式。

總結

`sp_stored_procedures` 列表、`sp_helptext` 閱讀、`OBJECT_DEFINITION` 程序化獲取、`sys.sql_modules` 全文搜索 — 四大工具組合，解決「找不到、看不懂、改不動」的存儲過程管理難題。配合元數據目錄建設，讓存儲過程資產可見、可控、可傳承。