아티클
04/01/2012

SQL 종속성 문제 해결

이 항목에서는 일반적인 개체 종속성 문제와 그 해결 방법에 대해 설명합니다.

sys.dm_sql_referenced_entities 동적 관리 함수가 열 수준 종속성을 반환하지 않는다

sys.dm_sql_referenced_entities 시스템 함수는 스키마 바운드 참조에 대한 열 수준 종속성을 보고합니다. 예를 들어 이 함수는 인덱싱된 뷰에 스키마 바인딩이 필요하기 때문에 인덱싱된 뷰에 대한 모든 열 수준 종속성을 보고합니다. 그러나 참조된 엔터티가 스키마 바인딩되지 않으면 열이 참조되는 모든 문을 바인딩할 수 있는 경우에만 열 종속성이 보고됩니다. 문은 해당 문이 구문 분석될 때 모든 개체가 존재하는 경우에만 성공적으로 바인딩할 수 있습니다. 엔터티에 정의된 문 중에 바인딩되지 못한 문이 있으면 열 종속성이 보고되지 않고 referenced_minor_id 열이 0을 반환합니다. 열 종속성을 확인할 수 없으면 오류 2020이 발생합니다. 이 오류가 발생해도 쿼리는 개체 수준 종속성을 반환합니다.

해결 방법

오류 2020보다 먼저 나타나는 메시지에서 확인된 모든 오류를 수정합니다. 예를 들어 다음 코드 예에서 Production.ApprovedDocuments 뷰는 Production.Document 테이블의 Title, ChangeNumber 및 Status 열에 정의됩니다. sys.dm_sql_referenced_entities 시스템 함수는 ApprovedDocuments 뷰가 종속된 개체와 열에 대해 쿼리됩니다. 이 뷰는 WITH SCHEMA_BINDING 절을 사용하여 만들어지지 않기 때문에 이 뷰에서 참조되는 열은 참조된 테이블에서 수정할 수 있습니다. 이 예에서는 Production.Document 테이블에 있는 ChangeNumber 열의 이름을 TrackingNumber로 변경합니다. 카탈로그 뷰는 ApprovedDocuments 뷰에 대해 다시 쿼리되지만 이 뷰에 정의된 모든 열에 바인딩할 수 없습니다. 그러면 문제를 나타내는 오류 207 및 2020이 반환됩니다. 이 문제를 해결하려면 열의 새 이름을 반영하도록 뷰를 변경해야 합니다.

USE AdventureWorks2008R2;
GO
CREATE VIEW Production.ApprovedDocuments
AS
    SELECT Title, ChangeNumber, Status
    FROM Production.Document
    WHERE Status = 2;
GO
SELECT referenced_schema_name AS schema_name
    ,referenced_entity_name AS table_name
    ,referenced_minor_name AS referenced_column
FROM sys.dm_sql_referenced_entities ('Production.ApprovedDocuments', 'OBJECT');
GO
EXEC sp_rename 'Production.Document.ChangeNumber', 'TrackingNumber', 'COLUMN';
GO
SELECT referenced_schema_name AS schema_name
    ,referenced_entity_name AS table_name
    ,referenced_minor_name AS referenced_column
FROM sys.dm_sql_referenced_entities ('Production.ApprovedDocuments', 'OBJECT');
GO

이 쿼리는 다음과 같은 오류 메시지를 반환합니다.

Msg 207, Level 16, State 1, Procedure ApprovedDocuments, Line 3

Invalid column name 'ChangeNumber'.

Msg 2020, Level 16, State 1, Line 1

The dependencies reported for entity "Production.ApprovedDocuments" do not include references to columns. This is either because the entity references an object that does not exist or because of an error in one or more statements in the entity. Before rerunning the query, ensure that there are no errors in the entity and that all objects referenced by the entity exist.

다음 예에서는 뷰의 열 이름을 수정합니다.

USE AdventureWorks2008R2;
GO
ALTER VIEW Production.ApprovedDocuments
AS
    SELECT Title,TrackingNumber, Status
    FROM Production.Document
    WHERE Status = 2;
GO

임시 테이블에 대한 조인을 포함하는 저장 프로시저 내부의 문에 대해서는 열 수준 종속성이 반환되지 않습니다. 여러 문으로 구성된 저장 프로시저의 경우 임시 테이블에 대한 조인을 포함하지 않는 문에 대해서만 열 수준 종속성이 반환됩니다. 임시 테이블에 조인하는 문에는 열 수준 종속성 보고 기능이 없습니다.

is_ambiguous 열이 사용자 정의 함수에 대해 일관되지 않은 값을 보고한다

is_ambiguous 열에 보고된 값이 사용자 정의 함수에 대해 일관되지 않은 것처럼 보일 수 있습니다. 카탈로그 뷰 sys.sql_expression_dependencies 및 동적 함수 sys.dm_sql_referenced_entities의 is_ambiguous 열은 해당 엔터티에 대한 참조가 모호함을 나타냅니다. 즉, 엔터티가 런타임에 사용자 정의 함수, UDT(사용자 정의 형식) 또는 xml 형식의 열에 대한 XQuery 참조로 확인될 수 있습니다. 사용자 정의 함수가 참조되는 방법에 따라 엔터티 유형이 명확하거나 명확하지 않을 수 있기 때문에 is_ambiguous 열은 1(true)로 설정될 수도 있고 0(false)으로 설정될 수도 있습니다. 예를 들어 다음과 같은 저장 프로시저가 있다고 가정해 보십시오.

CREATE PROCEDURE dbo.p1 
AS
    SELECT Sales.GetOrder() FROM t1;
    SELECT Sales.GetOrder();

첫 번째 SELECT 문에서는 Sales.GetOrder()가 Sales 스키마의 사용자 정의 함수인지 또는 GetOrder()라는 메서드가 있는 UDT 형식의 Sales 열인지 명확하지 않습니다. 이 경우 is_ambiguous 열은 참조된 엔터티 Sales.GetOrder()에 대해 1로 설정됩니다. 두 번째 SELECT 문에서는 Sales.GetOrder()에 대한 참조가 명확하며 구문에 따라 사용자 정의 함수에 대한 참조만 될 수 있습니다. 이 경우 is_ambiguous 열이 0으로 설정됩니다. 이 동작 때문에 is_ambiguous 열에 보고된 값이 일관되지 않은 것처럼 보일 수 있습니다. is_ambiguous 열의 값이 결정되는 방법을 이해하면 보고된 값을 명확하게 알 수 있습니다.

is_ambiguous 열은 다음과 같은 경우에 0(false)으로 설정됩니다.

참조가 사용자 정의 함수에 대한 것임이 명확한 경우. 즉, 쿼리가 사용자 정의 함수에 바인딩되고 해당 이름을 가진 UDT 열 메서드나 xml 형식의 열은 존재하지 않습니다.
참조가 UDT 열 메서드에 대한 것임이 명확한 경우. 즉, 해당 UDT 메서드가 있는 열이 존재하고 해당 이름을 가진 사용자 정의 함수나 xml 형식의 열은 존재하지 않습니다.

is_ambiguous 열은 다음과 같은 경우에 1(true)로 설정됩니다.

참조된 이름을 가진 사용자 정의 함수, 열 UDT 메서드 또는 xml 형식의 열이 존재하지 않는 경우
참조된 이름이 여러 엔터티에 사용되는 경우. 예를 들어 사용자 정의 함수의 이름과 UDT 열 메서드의 이름이 같습니다.

특성상 모호한 엔터티의 경우 referenced_database_name 및 referenced_schema_name 열이 올바르지 않을 수 있습니다. 예를 들어 다음과 같은 사용자 정의 함수가 있다고 가정해 보십시오.

CREATE FUNCTION GetNextEmpHierarchyId (@empname varchar(25))
RETURNS hierarchyid
AS
BEGIN
    RETURN 
(
    SELECT h.empid.GetDescendant((SELECT MAX(h1.empid)  
                                  FROM dbo.Employees AS h1  
                                  WHERE h1.empid.GetAncestor(1) = h.empid), NULL)
    FROM dbo.Employees AS h  
    WHERE h.empname = @empname  
)  
END;

hierarchyid UDT 메서드 호출 때문에 referenced_database_name 및 referenced_schema_name 열은 이 함수에 적합하지 않습니다. 따라서 h.empid.GetDescendant 및 h1.empid.GetAncestor 에 대한 참조가 세 부분으로 된 이름(database.schema.object)을 사용하는 엔터티에 대한 참조인지 또는 UDT 메서드(table.column.method)에 대한 참조인지 명확하지 않습니다.

해결 방법

사용자가 조치할 필요는 없습니다.

referenced_id 열이 데이터베이스 간 종속성에 대해 보고되지 않는다

referenced_id 열이 sys.sql_expression_dependencies 카탈로그 뷰의 데이터베이스 간 참조에 대해 확인되지 않습니다. 데이터베이스 이름과 스키마 이름은 해당 이름을 명시적으로 지정할 경우에만 기록됩니다. 예를 들어 MyDB.MySchema.MyTable로 지정된 경우 데이터베이스 및 스키마 이름이 기록되지만 MyDB..MyTable로 지정된 경우에는 데이터베이스 이름만 기록됩니다.

referenced_id는 참조된 엔터티를 성공적으로 바인딩할 수 있는 경우에만 sys.dm_sql_referenced_entities 시스템 함수의 데이터베이스 간 참조에 대해 보고됩니다. 바인딩은 다음을 비롯한 여러 이유로 인해 실패할 수 있습니다.

데이터베이스가 오프라인 상태인 경우
참조된 엔터티가 데이터베이스에 없는 경우

해결 방법

데이터베이스가 온라인 상태이고 참조된 엔터티가 데이터베이스에 있는지 확인합니다.

referenced_id 열이 데이터베이스 내에서 참조된 엔터티에 대해 Null이다

sys.dm_sql_referenced_entities 시스템 함수 및 sys.sql_expression_dependencies 시스템 뷰는 모든 스키마 바운드 참조된 엔터티의 ID를 보고합니다. 그러나 참조된 엔터티의 ID를 확인할 수 없는 경우 referenced_id 열은 데이터베이스 내의 비스키마 바운드 참조에 대해 NULL입니다. 이 문제는 다음과 같은 경우에 발생할 수 있습니다.

참조된 엔터티가 데이터베이스에 없는 경우
이름 확인이 호출자에 종속된 경우. 이 경우 is_caller_dependent 열이 1으로 설정됩니다.

해결 방법

참조된 엔터티가 데이터베이스에 있는지 확인합니다. 엔터티가 없으면 엔터티를 만들고 엔터티가 있으면 다음과 같은 요구 사항을 충족하는지 확인합니다.

참조된 엔터티 이름의 철자가 정확해야 합니다.
지정된 이름이 데이터베이스의 데이터 정렬 요구 사항을 충족해야 합니다. 데이터베이스에서 대/소문자 구분 데이터 정렬을 사용하면 지정된 이름과 개체 이름의 대/소문자가 정확히 일치해야 합니다. 예를 들어 SalesHistory 개체의 ID를 saleshistory로 지정하면 대/소문자 구분 데이터 정렬을 사용하는 데이터베이스에서 이 개체를 찾을 수 없습니다.
개체의 스키마 이름을 지정해야 합니다. 개체가 호출자의 기본 스키마, sys 스키마 또는 dbo 스키마에 없으면 두 부분으로 된 이름(schema_name.object_name)이 필요합니다.

위의 요구 사항을 충족하도록 참조 엔터티의 정의를 수정합니다.

참조된 엔터티가 호출자에 종속된 경우 참조된 엔터티에 두 부분으로 된 이름을 지정하여 참조 엔터티의 정의를 수정합니다. 호출자 종속 참조에 대한 자세한 내용은 SQL 종속성 보고를 참조하십시오.

종속성 정보가 master 데이터베이스의 개체에 대해 보고되지 않는다

master 데이터베이스에서 만든 사용자 정의 엔터티에 대해서는 SQL 종속성이 만들어지고 유지 관리됩니다. 엔터티에 대한 SQL 종속성이 보고되지 않으면 다음 단계를 수행하십시오.

엔터티가 종속성 추적에 적합한 유형인지 확인합니다.

모든 사용자 개체에 대해 종속성 정보가 추적되지는 않습니다. 종속성 정보가 생성되어 유지 관리되는 엔터티 유형의 목록은 SQL 종속성 이해를 참조하십시오.
엔터티가 시스템 개체로 표시되지 않았는지 확인합니다.

sys.objects 카탈로그 뷰의 엔터티에 대해 is_ms_shipped 열을 쿼리합니다. 이 열이 1로 설정된 경우 이 엔터티는 SQL Server와 함께 제공되는 시스템 개체이거나 수동으로 이 열을 1로 설정하여 시스템 개체를 가장하도록 수정된 사용자 정의 개체입니다.

해결 방법

개체가 지원되지 않는 유형이면 종속성 정보를 사용할 수 없습니다.

시스템 개체에 대한 종속성은 추적되지 않습니다. 엔터티가 사용자 정의 엔터티인 경우 SQL Server에서 이 엔터티에 대한 종속성을 만들고 유지 관리하도록 하려면 is_ms_shipped column 열을 0으로 다시 설정해야 합니다.