萬盛學電腦網

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

SQL Server 重命名函數/預存程序的隱藏陷阱:sys.all_sql_modules 定義不同步

問題現象

使用 SSMS 右鍵「重新命名」或 sp_rename 修改函數/預存程序名稱後:

  • 物件在物件總管顯示新名稱
  • 執行 sp_helptext 新名稱 顯示的定義內容,CREATE 陳述式內仍為舊名稱
  • 查詢 sys.all_sql_modules.definition 同樣殘留舊名稱

若直接拿此定義部署至生產環境,將建立出錯誤名稱的物件!

重現步驟

-- 1. 建立測試函數
CREATE FUNCTION dbo.ufn_test_1 () RETURNS CHAR(1) AS BEGIN RETURN ('F'); END;
GO

-- 2. 查詢定義(正常)
EXEC sp_helptext 'dbo.ufn_test_1';
SELECT definition FROM sys.all_sql_modules WHERE object_id = OBJECT_ID('dbo.ufn_test_1');

-- 3. SSMS 右鍵重新命名為 ufn_test_2

-- 4. 再次查詢定義 → 發現 CREATE FUNCTION dbo.ufn_test_1 ... 仍為舊名!

根本原因

  • sp_rename 僅更新系統目錄中的 name 欄位(sys.objects.name)
  • sys.all_sql_modules.definition 儲存的是建立時的原始腳本文本,不會自動同步更新
  • 編譯後的中繼資料(查詢計劃、參數資訊)已正確對應新名稱,僅原始碼文本不同步

解決方案

方法一:重建物件(最徹底)

DROP FUNCTION dbo.ufn_test_2;
GO
CREATE FUNCTION dbo.ufn_test_2 () RETURNS CHAR(1) AS BEGIN RETURN ('F'); END;
GO

方法二:SSMS 正確操作流程

  1. 右鍵物件 → 修改 — 會載入最新定義至查詢視窗
  2. 手動將腳本內 CREATE FUNCTION 舊名 改為 CREATE FUNCTION 新名
  3. 執行腳本(隱含 DROP + CREATE)

方法三:腳本產生精靈

  • 右鍵資料庫 → 任務 → 產生腳本 → 選擇特定物件 → 產出的腳本會使用當前正確名稱

最佳實踐建議

場景建議做法
開發初期頻繁改名直接 DROP + CREATE 重建,避免殘留舊定義
生產環境重命名嚴禁直接 sp_rename;必須走變更流程:腳本重建 + 部署驗證
自動化部署腳本產生查詢 sys.all_sql_modules 前,先核對 definition 內名稱是否與 sys.objects.name 一致
大量物件重命名建立對照表,批次產生 DROP/CREATE 腳本,單一交易執行

結語

sp_rename 是元資料層級操作,不觸及原始碼定義文本。永遠不要信任 sp_helptext 或 sys.all_sql_modules 產出的 CREATE 陳述式中的名稱,除非物件從未被重命名過。部署腳本請使用 SSMS「產生腳本」功能,或手動驗證修正後再執行。

copyright © 萬盛學電腦網 all rights reserved