sp_refreshsqlmodule(Transact-SQL)

아티클
12/22/2023

적용 대상:SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics(전용 SQL 풀에만 해당)

현재 데이터베이스에서 지정된 비 스키마 바인딩 저장 프로시저, 사용자 정의 함수, 뷰, DML 트리거, 데이터베이스 수준 DDL 트리거 또는 서버 수준 DDL 트리거에 대한 메타데이터를 업데이트. 기본 개체가 변경되면 매개 변수의 데이터 형식과 같은 이러한 개체의 영구 메타데이터가 최신 상태를 유지하지 못할 수 있습니다. 예를 들어 다음과 같은 The definition for user-defined data type 'typename' has changed오류가 표시될 수 있습니다. 오류에 지정된 형식을 사용하는 모듈에 대한 메타데이터를 새로 고쳐 문제를 해결할 수 있습니다.

Transact-SQL 구문 표기 규칙

구문

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

인수

@name

저장 프로시저, 사용자 정의 함수, 뷰, DML 트리거, 데이터베이스 수준 DDL 트리거 또는 서버 수준 DDL 트리거의 이름입니다. module_name CLR(공용 언어 런타임) 저장 프로시저 또는 CLR 함수일 수 없습니다. module_name 스키마 바인딩할 수 없습니다. module_name 기본값이 없는 nvarchar입니다. module_name 다중 부분 식별자일 수 있지만 현재 데이터베이스의 개체만 참조할 수 있습니다.

@namespace

지정된 모듈의 클래스입니다. module_name DDL 트리거인 <경우 클래스>가 필요합니다. <클래스>는 nvarchar(20)입니다. 잘못된 입력:

DATABASE_DDL_TRIGGER
SERVER_DDL_TRIGGER - 적용 대상: SQL Server 2008(10.0.x) 이상

반환 코드 값

0(성공) 또는 0이 아닌 숫자(실패)

설명

정의에 영향을 주는 모듈의 기본 개체를 변경할 때 시스템 저장 프로시저 sp_refreshsqlmodule 를 실행해야 합니다. 그렇지 않으면 모듈이 쿼리되거나 호출될 때 예기치 않은 결과가 생성될 수 있습니다. 보기를 새로 고치려면 동일한 결과를 사용하거나 sp_refreshview 사용할 sp_refreshsqlmodule 수 있습니다.

sp_refreshsqlmodule 는 개체와 연결된 사용 권한, 확장 속성 또는 SET 옵션에 영향을 주지 않습니다.

서버 수준 DDL 트리거를 새로 고치려면 데이터베이스의 컨텍스트에서 이 저장 프로시저를 실행합니다.

참고 항목

개체와 연결된 모든 서명은 실행할 sp_refreshsqlmodule때 삭제됩니다.

사용 권한

모듈에 대한 ALTER 권한 및 개체가 참조하는 CLR 사용자 정의 형식과 XML 스키마 컬렉션에 대한 REFERENCES 권한이 필요합니다. 지정된 모듈이 데이터베이스 수준 DDL 트리거인 경우 현재 데이터베이스에서 ALTER ANY DATABASE DDL TRIGGER 권한이 필요합니다. 지정된 모듈이 서버 수준 DDL 트리거인 경우 CONTROL SERVER 권한이 필요합니다.

또한 EXECUTE AS 절로 정의된 모듈의 경우 지정된 보안 주체에 대한 IMPERSONATE 권한이 필요합니다. 일반적으로 개체를 새로 고치는 경우 모듈이 EXECUTE AS USER로 정의되고 보안 주체의 사용자 이름이 모듈을 만들 때와 다른 사용자로 확인되지 않는 한 EXECUTE AS 보안 주체는 변경되지 않습니다.

예제

A. 사용자 정의 함수 새로 고침

다음 예제에서는 사용자 정의 함수를 새로 고칩니다. 이 예에서는 별칭 데이터 형식인 mytype과 to_upper을 사용하는 사용자 정의 함수 mytype를 만듭니다. 그런 다음 이름이 mytype 바뀝 myoldtype니다. 그러면 다른 정의가 있는 새 mytype 항목이 만들어집니다. dbo.to_upper 함수는 이전 구현 대신 새 구현mytype을 참조할 수 있도록 새로 고쳐집니다.

-- Create an alias type.  
USE AdventureWorks2022;  
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. 데이터베이스 수준 DDL 트리거 새로 고침

다음 예제에서는 데이터베이스 수준 DDL 트리거를 새로 고칩니다.

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

C. 서버 수준 DDL 트리거 새로 고침

다음 예에서는 서버 수준 DDL 트리거를 새로 고칩니다.

적용 대상: SQL Server 2008(10.0.x) 이상

USE master;  
GO  
EXEC sys.sp_refreshsqlmodule @name = 'ddl_trig_database' , @namespace = 'SERVER_DDL_TRIGGER';  
GO