sp_refreshsqlmodule (Transact-SQL)sp_refreshsqlmodule (Transact-SQL)

適用於: 是SQL Server 是Azure SQL Database 是Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

針對目前資料庫中指定的非結構描述繫結預存程序、使用者自訂函數、檢視、DML 觸發程序、資料庫層級 DDL 觸發程序或伺服器層級 DDL 觸發程序,更新其中繼資料。Updates the metadata for the specified non-schema-bound stored procedure, user-defined function, view, DML trigger, database-level DDL trigger, or server-level DDL trigger in the current database. 這些物件的基礎物件變更之後,其保存中繼資料也可能會過期,例如參數的資料類型。Persistent metadata for these objects, such as data types of parameters, can become outdated because of changes to their underlying objects.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

  
sys.sp_refreshsqlmodule [ @name = ] 'module_name'   
    [ , [ @namespace = ] ' <class> ' ]  
  
<class> ::=  
{  
  | DATABASE_DDL_TRIGGER  
  | SERVER_DDL_TRIGGER  
}  
  

引數Arguments

[ @name = ] 'module\_name' 是預存程序、 使用者定義函數、 檢視、 DML 觸發程序、 資料庫層級 DDL 觸發程序或伺服器層級 DDL 觸發程序的名稱。[ @name = ] 'module\_name' Is the name of the stored procedure, user-defined function, view, DML trigger, database-level DDL trigger, or server-level DDL trigger. module_name不能是 common language runtime (CLR) 預存程序或 CLR 函式。module_name cannot be a common language runtime (CLR) stored procedure or a CLR function. module_name不可進行結構描述繫結。module_name cannot be schema-bound. module_namenvarchar,沒有預設值。module_name is nvarchar, with no default. module_name可以是多重部分識別碼,但只能參考目前資料庫中的物件。module_name can be a multi-part identifier, but can only refer to objects in the current database.

[ , @namespace = ] ' \<class> ' 為指定的模組類別。[ , @namespace = ] ' \<class> ' Is the class of the specified module. module_name DDL 觸發程序,<類別 > 為必要。When module_name is a DDL trigger, <class> is required. <類別 >nvarchar(20)。<class> is nvarchar(20). 有效輸入包括:Valid inputs are:

DATABASE_DDL_TRIGGERDATABASE_DDL_TRIGGER
SERVER_DDL_TRIGGERSERVER_DDL_TRIGGER 適用於SQL Server 2008SQL Server 2008SQL Server 2019SQL Server 2019Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2019SQL Server 2019.

傳回碼值Return Code Values

0 (成功) 或非零數字 (失敗)0 (success) or a nonzero number (failure)

備註Remarks

sp_refreshsqlmodule變更會影響其定義的物件基礎的模組時,應該執行。sp_refreshsqlmodule should be run when changes are made to the objects underlying the module that affect its definition. 否則,在查詢或叫用模組時,可能會產生非預期的結果。Otherwise, the module might produce unexpected results when it is queried or invoked. 若要重新整理檢視,您可以使用其中一個sp_refreshsqlmodule或是sp_refreshview相同的結果。To refresh a view, you can use either sp_refreshsqlmodule or sp_refreshview with the same results.

sp_refreshsqlmodule不會影響任何權限、 擴充的屬性或設定與物件相關聯的選項。sp_refreshsqlmodule does not affect any permissions, extended properties, or SET options that are associated with the object.

若要重新整理伺服器層級 DDL 觸發程序,請從任何資料庫的內容執行此預存程序。To refresh a server-level DDL trigger, execute this stored procedure from the context of any database.

注意

當您執行時,會卸除與物件相關聯的任何簽章sp_refreshsqlmoduleAny signatures that are associated with the object are dropped when you run sp_refreshsqlmodule.

PermissionsPermissions

需要模組的 ALTER 權限,以及物件所參考之任何 CLR 使用者自訂型別和 XML 結構描述集合的 REFERENCES 權限。Requires ALTER permission on the module and REFERENCES permission on any CLR user-defined types and XML schema collections that are referenced by the object. 當指定的模組是資料庫層級 DDL 觸發程序時,便需要目前資料庫的 ALTER ANY DATABASE DDL TRIGGER 權限。Requires ALTER ANY DATABASE DDL TRIGGER permission in the current database when the specified module is a database-level DDL trigger. 當指定的模組是伺服器層級 DDL 觸發程序時,便需要 CONTROL SERVER 權限。Requires CONTROL SERVER permission when the specified module is a server-level DDL trigger.

此外,對於使用 EXECUTE AS 子句定義的模組,也必須對指定的主體具備 IMPERSONATE 權限。Additionally, for modules that are defined with the EXECUTE AS clause, IMPERSONATE permission is required on the specified principal. 一般而言,重新整理物件不會變更其 EXECUTE AS 主體,除非模組是使用 EXECUTE AS USER 定義,而且主體的使用者名稱這時解析出來不是模組建立當時的使用者。Generally, refreshing an object does not change its EXECUTE AS principal, unless the module was defined with EXECUTE AS USER and the user name of the principal now resolves to a different user than that at the time the module was created.

範例Examples

A.A. 重新整理使用者自訂函數Refreshing a user-defined function

下列範例會重新整理使用者自訂函數。The following example refreshes a user-defined function. 這個範例會建立別名資料類型 mytype,以及使用 to_upper 的使用者自訂函數 mytypeThe example creates an alias data type, mytype, and a user-defined function, to_upper, that uses mytype. 接著,mytype 會重新命名為 myoldtype,而且會建立有不同定義的新 mytypeThen, mytype is renamed to myoldtype, and a new mytype is created that has a different definition. dbo.to_upper 函數會重新整理,以參考 mytype 的新實作,而非舊實作。The dbo.to_upper function is refreshed so that it references the new implementation of mytype, instead of the old one.

-- Create an alias type.  
USE AdventureWorks2012;  
GO  
IF EXISTS (SELECT 'mytype' FROM sys.types WHERE name = 'mytype')  
DROP TYPE mytype;  
GO  
  
CREATE TYPE mytype FROM nvarchar(5);  
GO  
  
IF OBJECT_ID ('dbo.to_upper', 'FN') IS NOT NULL  
DROP FUNCTION dbo.to_upper;  
GO  
  
CREATE FUNCTION dbo.to_upper (@a mytype)  
RETURNS mytype  
WITH ENCRYPTION  
AS  
BEGIN  
RETURN upper(@a)  
END;  
GO  
  
SELECT dbo.to_upper('abcde');  
GO  
  
-- Increase the length of the alias type.  
sp_rename 'mytype', 'myoldtype', 'userdatatype';  
GO  
  
CREATE TYPE mytype FROM nvarchar(10);  
GO  
  
-- The function parameter still uses the old type.  
SELECT name, type_name(user_type_id)   
FROM sys.parameters   
WHERE object_id = OBJECT_ID('dbo.to_upper');  
GO  
  
SELECT dbo.to_upper('abcdefgh'); -- Fails because of truncation  
GO  
  
-- Refresh the function to bind to the renamed type.  
EXEC sys.sp_refreshsqlmodule 'dbo.to_upper';  
  
-- The function parameters are now bound to the correct type and the statement works correctly.  
SELECT name, type_name(user_type_id) FROM sys.parameters  
WHERE object_id = OBJECT_ID('dbo.to_upper');  
GO  
  
SELECT dbo.to_upper('abcdefgh');  
GO  

B.B. 重新整理資料庫層級 DDL 觸發程序Refreshing a database-level DDL trigger

下列範例會重新整理資料庫層級 DDL 觸發程序。The following example refreshes a database-level DDL trigger.

USE AdventureWorks2012;  
GO  
EXEC sys.sp_refreshsqlmodule @name = 'ddlDatabaseTriggerLog' , @namespace = 'DATABASE_DDL_TRIGGER';  
GO  

C.C. 重新整理伺服器層級 DDL 觸發程序Refreshing a server-level DDL trigger

下列範例會重新整理伺服器層級 DDL 觸發程序。The following example refreshes a server-level DDL trigger.

適用於SQL Server 2008SQL Server 2008SQL Server 2019SQL Server 2019Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2019SQL Server 2019.
USE master;  
GO  
EXEC sys.sp_refreshsqlmodule @name = 'ddl_trig_database' , @namespace = 'SERVER_DDL_TRIGGER';  
GO  
  

另請參閱See Also

sp_refreshview (Transact-SQL) sp_refreshview (Transact-SQL)
Database Engine 預存程序(Transact SQL)Database Engine Stored Procedures (Transact-SQL)