示範版本：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
參考資料
sp_refreshsqlmodule (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/bb326754.aspx
sp_refreshview (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms187821.aspx
sys.parameters (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms176074.aspx
%E8%88%87%E9%8E%96%E5%AE%9A+(Lock).png){kind=spacer}
%E4%BB%A5%E5%8F%8A%E5%AD%97%E5%85%83%E6%95%B8.png)
%E3%80%8D.png)
.png)
.png)
.png)
%E3%80%8D%EF%BC%9A%E5%8D%BB%E5%8F%AA%E6%9C%89+2+%E5%80%8B%E8%B3%87%E6%96%99%E8%A1%8C.png)
