德瑞克：SQL Server 學習筆記: 使用 sp_refreshsqlmodule 更新資料庫物件的中繼資料

示範版本：SQL Server 2012

sp_refreshsqlmodule

針對目前資料庫中指定的非結構描述繫結預存程序、使用者自訂函數、檢視、DML 觸發程序、資料庫層級 DDL 觸發程序或伺服器層級 DDL 觸發程序，更新其中繼資料。

這些物件的基礎物件變更之後，其保存中繼資料也可能會過期，例如參數的資料類型。

sp_refreshsqlmodule 應在模組的基礎物件變更而影響其定義時執行。

否則，在查詢或叫用模組時，可能會產生非預期的結果。

若要重新整理檢視，您可以使用 sp_refreshsqlmodule 或 sp_refreshview 來重新整理相同的結果。

sp_refreshsqlmodule 不會影響與物件相關聯的任何權限、擴充屬性或 SET 選項。

若要重新整理伺服器層級 DDL 觸發程序，請從任何資料庫的內容執行此預存程序。

當您執行 sp_refreshsqlmodule 時，會卸除與物件相關聯的任何簽章。

EX1. 重新整理使用者預存預存程序的中繼資料

下列範例會重新整理使用者預存預存程序的中繼資料。

這個範例會建立別名資料類型：uid，以及使用 uid 的預存程序：usp_udt01。
接著，uid 會重新命名為 uid_old，而且會建立有不同定義的新 uid。

所以，需要對預存程序：usp_udt01 執行中繼資料的重新整理，以參考 uid 的新實作，而非舊實作。

-- 01_建立「別名資料類型(alias data type)」：uid，其資料類型是：char(3)。
USE Northwind_Dev
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_udt01]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_udt01]
GO
IF  EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'uid' AND ss.name = N'dbo')
DROP TYPE [dbo].[uid]
GO
CREATE TYPE [dbo].[uid] FROM [char](3) NOT NULL
GO

-- 圖01_建立「別名資料類型(alias data type)」

-- 02 僅查詢：別名資料類型、系統資料類型、最大長度(Byte)以及字元數

SELECT ty1.name N'資料類型', ty2.name N'系統資料類型',ty1.max_length N'資料行的最大長度(Byte)',
 CAST(CASE WHEN ty2.name IN (N'nchar', N'nvarchar') AND ty1.max_length <> -1 
  THEN ty1.max_length/2 ELSE ty1.max_length END AS int) N'字元數'
FROM sys.types ty1 LEFT OUTER JOIN sys.types ty2
ON (ty2.user_type_id = ty1.system_type_id and ty2.user_type_id = ty2.system_type_id) or ((ty2.system_type_id = ty1.system_type_id) 
 and (ty2.user_type_id = ty1.user_type_id) and (ty2.is_user_defined = 0) and (ty2.is_assembly_type = 1)) 
WHERE ty1.is_user_defined=1
GO

-- 圖02_查詢：別名資料類型、系統資料類型、最大長度(Byte)以及字元數

-- 03_建立預存程序：usp_udt01，使用此「別名資料類型」。

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_udt01]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_udt01]
GO
CREATE PROCEDURE usp_udt01
 @cid uid 
AS
 SELECT @cid AS N'別名資料類型'
GO

-- 04_執行此預存程序：usp_udt01，輸入的資料長度是 3 個字元

EXEC usp_udt01 'A12'
GO

-- 圖03_執行此預存程序：usp_udt01

-- 05_若嘗試刪除此「別名資料類型」：uid，會遭遇到以下的錯誤訊息：

IF  EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'uid' AND ss.name = N'dbo')
DROP TYPE [dbo].[uid]
GO

/*
錯誤訊息：

訊息 3732，層級 16，狀態 1，行 2
無法卸除類型 'dbo.uid'，因為物件 'usp_udt01' 正在參考它。可能還有參考此類型的其他物件。
*/

-- 圖04_若嘗試刪除此「別名資料類型」：uid，會遭遇到錯誤訊息

無法刪除已經被使用的「別名資料類型」：uid，但可以修改其名稱。

-- 06_先修改「別名資料類型」：uid的名稱為：uid_old

EXEC sp_rename @objname= 'uid', @newname = 'uid_old', @objtype= 'USERDATATYPE';
GO

/*
產生的訊息：

注意: 變更物件名稱的任何部分將會中斷指令碼和預存程序。
*/

-- 圖05_先修改「別名資料類型」：uid的名稱為：uid_old

-- 07_建立與原先同名的「別名資料類型(alias data type)」：uid，但其資料類型是：char(5)。

USE Northwind_Dev
GO
IF  EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'uid' AND ss.name = N'dbo')
DROP TYPE [dbo].[uid]
GO
CREATE TYPE [dbo].[uid] FROM [char](5) NOT NULL
GO

-- 08_再度查詢：別名資料類型、系統資料類型、最大長度(Byte)以及字元數

SELECT ty1.name N'資料類型', ty2.name N'系統資料類型',ty1.max_length N'資料行的最大長度(Byte)',
 CAST(CASE WHEN ty2.name IN (N'nchar', N'nvarchar') AND ty1.max_length <> -1 
  THEN ty1.max_length/2 ELSE ty1.max_length END AS int) N'字元數'
FROM sys.types ty1 LEFT OUTER JOIN sys.types ty2
ON (ty2.user_type_id = ty1.system_type_id and ty2.user_type_id = ty2.system_type_id) or ((ty2.system_type_id = ty1.system_type_id) 
 and (ty2.user_type_id = ty1.user_type_id) and (ty2.is_user_defined = 0) and (ty2.is_assembly_type = 1)) 
WHERE ty1.is_user_defined=1
GO

-- 圖06_有兩個別名資料類型

-- 查詢接受參數之物件的每個參數
SELECT *
FROM sys.parameters 

-- 08_檢查使用者預存程序：usp_udt01，卻是使用舊的「別名資料類型」：uid_old，其資料類型是：char(3)。
SELECT OBJECT_NAME(object_id) AS N'物件名稱', name AS N'參數名稱', TYPE_NAME(user_type_id) AS N'資料類型',
 max_length AS N'最大長度 (位元組)', precision AS N'有效位數', scale AS N'小數位數', is_output AS N'OUTPUT參數', 
 has_default_value AS N'是否有預設值', default_value AS N'預設值', is_readonly AS N'READONLY參數'
FROM sys.parameters 
ORDER BY 1 DESC
GO

-- 圖07_檢查使用者預存程序：usp_udt01，卻是使用舊的「別名資料類型」：uid_old，其資料類型是：char(3)

-- 圖08_使用 SSMS觀察使用者預存程序：usp_udt01，其系統資料沒有更新，仍是使用「別名資料類型」：uid_old，但其資料類型是：char(3)

-- 09_執行此預存程序：usp_udt01，輸入的資料長度是 5 個字元，遭遇錯誤

EXEC usp_udt01 'A12' -- 三個字元
GO
EXEC usp_udt01 'A1234' -- 五個字元
GO

/*
錯誤訊息：

訊息 496，層級 16，狀態 1，程序 usp_udt01，行 4
"@cid" 參數的類型和建立時的類型不同。請先卸除，再用兩段式類型名稱重新建立程序。或使用 sp_refreshsqlmodule 重新整理它的參數中繼資料。
訊息 496，層級 16，狀態 1，程序 usp_udt01，行 4
"@cid" 參數的類型和建立時的類型不同。請先卸除，再用兩段式類型名稱重新建立程序。或使用 sp_refreshsqlmodule 重新整理它的參數中繼資料。

*/

-- 圖09_輸入的資料長度是 3或是 5 個字元，遭遇錯誤

-- 10_使用 sys.sp_refreshsqlmodule 更新其中繼資料
EXEC sys.sp_refreshsqlmodule @name = 'dbo.usp_udt01' ;
GO

-- 11_檢查使用者預存程序：usp_udt01，已經改用新的「別名資料類型」：uid_old，其資料類型是：char(5)。
SELECT OBJECT_NAME(object_id) AS N'物件名稱', name AS N'參數名稱', TYPE_NAME(user_type_id) AS N'資料類型',
 max_length AS N'最大長度 (位元組)', precision AS N'有效位數', scale AS N'小數位數', is_output AS N'OUTPUT參數', 
 has_default_value AS N'是否有預設值', default_value AS N'預設值', is_readonly AS N'READONLY參數'
FROM sys.parameters 
ORDER BY 1 DESC
GO

-- 圖10_使用者預存程序：usp_udt01，已經改用新的「別名資料類型」

-- 圖11_使用 SSMS 觀察使用者預存程序：usp_udt01，已經改用新的資料類型：char(5)

-- 12_執行此預存程序：usp_udt01，輸入的資料長度是 5 個字元，正常執行

EXEC usp_udt01 'A1234'
GO

-- 圖12_執行此預存程序：usp_udt01，輸入的資料長度是 5 個字元，正常執行

-- 99_回復原狀

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_udt01]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_udt01]
GO
IF  EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'uid' AND ss.name = N'dbo')
DROP TYPE [dbo].[uid]
GO
IF  EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'uid_old' AND ss.name = N'dbo')
DROP TYPE [dbo].[uid_old]
GO