참조 개체 확인 (sys.sql_expression_dependencies)

참조 개체 확인 (sys.sql_expression_dependencies)

 

• Version : SQL Server 2008, 2008R2, 2012

 

데이터베이스에서 엔티티의 참조 관계를 확인 하는 방법을 알아 보자. 사용자 정의 엔티티는 이름별 종속성 마다 한 개의 행을 가지고 있다. 두 엔터티 간의 종속성은 한 엔티티가 참조 엔티티라고 하는 다른 엔티티의 영구 SQL 식에 이름별로 나타나는 경우 생성된다. 예를 들어 뷰 정의에서 테이블을 참조하면 참조 엔터티인 뷰는 참조된 엔터티인 테이블에 종속됩니다. 테이블이 삭제되면 뷰를 사용할 수 없다.

 

실습을 통해서 참조된 엔티티를 확인 하는 방법을 알아 보자.

 

[SSMS에서 확인하기]

종속성을 확인하려는 개체에서 마우스 오른쪽을 클릭하여 [종속성 보기]를 선택 한다.

 

[게체 종속성] 창이 나타나면 해당 개체가 종속된 엔티티 또는 종속하고 있는 엔티티를 확인 할 수 있다.

 

 

 

[T-SQL 확인하기]

아래 스크립트를 통하여 엔티티간의 종속성을 확인 할 수 있다.

• 스키마 바운드 엔티티
• 비스키마 바운드 엔티티
• 데이터베이스 간 및 서버 간 엔터티. (엔티티 이름이 확인 할 수 있지만 엔티티 ID는 확인되지 않음.)
• 스키마 바운드 엔터티에 대한 열 수준 종속성.
• master 데이터베이스의 컨텍스트에서 서버 수준 DLL 트리거

 

 

select * from sys.sql_expression_dependencies

 

 

열이름	데이터 형식	설명
referencing_id	int	참조 엔티티ID
referencing_minor_id	int	참조 엔티티가 열인 경우 열ID, 아닐 경우 0
referencing_class	tinyint	참조 엔티티의 클래스, 1 = 개체 또는 열, 12 = 데이터베이스 DDL 트리거, 13 = 서버 DDL 트리거
referencing_class_desc	nvarchar(60)	참 엔티티 클래스에 대한 설명
is_schema_bound_reference	bit	1 = 참조된 엔티티가 스키마 바운드 0 = 참조된 엔티티가 비스키마 바운드
referenced_class	tinyint	참조된 엔티티의 클래스 1 = 개체 또는 열 6 = 형식 10 = XML 스키마 컬렉션 21 = 파티션 함수
referenced_class_desc	nvarchar(60)	참조된 엔티티의 클래스에 대한 설명
referenced_server_name	sysname
referenced_database_name	sysname	참조된 엔티티의 데이터베이스 이름
referenced_schema_name	sysname	참조된 엔티티가 속한 스키마
referenced_entity_name	sysname	참조된 엔티티의 이름
referenced_id	int	참조된 엔티티의 ID
referenced_minor_id	int	참조 엔티티가 열인 경우 참조된 열의 ID, 아닐 경우 0
is_caller_dependent	bit	1 = 참조 엔티티가 호출자에 종속되고 런타임에 확인 0 = 참조 에엔티티 ID가 호출자에 종속되지 않음
is_ambiguous	bit	참조가 모호하며 런타임에 사용자 정의 함수, UDT(사용자 정의 형식) 또는 xml 형식의 열에 대한 XQuery 참조로 확인할 수 있음을 나타냄 0 = 참조가 모호함 1 = 참조가 명확하거나 뷰를 호출 할 때 엔티티를 바인딩 할 수 있다. 스키마 바운드 참조경우 항상 0

 

 

[Production.vProductAndDescription 뷰를 참조한 엔티티 확인]

USE AdventureWorks2008R2; GO SELECT OBJECT_NAME(referencing_id) AS referencing_entity_name, o.type_desc AS referencing_desciption, COALESCE(COL_NAME(referencing_id, referencing_minor_id), '(n/a)') AS referencing_minor_id, referencing_class_desc, referenced_class_desc, referenced_server_name, referenced_database_name, referenced_schema_name, referenced_entity_name, COALESCE(COL_NAME(referenced_id, referenced_minor_id), '(n/a)') AS referenced_column_name, is_caller_dependent, is_ambiguous FROM sys.sql_expression_dependencies AS sed INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id WHERE referencing_id = OBJECT_ID(N'Production.vProductAndDescription'); GO

 

 

 

 

[Production.Product 테이블을 참조하는 엔티티 확인]

USE AdventureWorks2008R2; GO SELECT OBJECT_SCHEMA_NAME ( referencing_id ) AS referencing_schema_name, OBJECT_NAME(referencing_id) AS referencing_entity_name, o.type_desc AS referencing_desciption, COALESCE(COL_NAME(referencing_id, referencing_minor_id), '(n/a)') AS referencing_minor_id, referencing_class_desc, referenced_class_desc, referenced_server_name, referenced_database_name, referenced_schema_name, referenced_entity_name, COALESCE(COL_NAME(referenced_id, referenced_minor_id), '(n/a)') AS referenced_column_name, is_caller_dependent, is_ambiguous FROM sys.sql_expression_dependencies AS sed INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id WHERE referenced_id = OBJECT_ID(N'Production.Product'); GO

 

 

 

개체 종속성 확인(참조 개체 확인)은 해당 테이블의 수정이나 삭제 시 영향 받는 테이블의 관계를 쉽게 확인 할 수 있다. 또한 재해복구 시나리오에서 커럽션 발생 시 해당 테이블의 영향력 파급을 확인 하는데 응요 할 수도 있다.

 

[참고링크]

http://msdn.microsoft.com/ko-kr/library/bb677315.aspx

 

 

2013-03-25 / 강성욱 / http://sqlmvp.kr