搜尋本站文章

2013-03-28

使用 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





參考資料


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