데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

참조 개체 확인 (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

 



강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
1750 SQL Server 인증 실패시 반환되는 클라이언트 메시지 정보 jevida(강성욱) 2016.09.15 3626
1749 SQL Server에 할당된 메모리 개체 확인 jevida(강성욱) 2016.09.15 1582
1748 SQL Server 비동기 업데이트 활성 / 비활성에 따른 특성 jevida(강성욱) 2016.09.15 1694
1747 DBCC CHECKDB와 Compute Column 인덱스의 성능 관계 jevida(강성욱) 2016.09.15 1432
1746 Collation에 따른 DMV 실행 오류 jevida(강성욱) 2016.09.15 1308
» 참조 개체 확인 (sys.sql_expression_dependencies) jevida(강성욱) 2016.09.15 1560
1744 특정 테이블의 마지막 접근 시간 알아보기 jevida(강성욱) 2016.09.15 1237
1743 SQL Server Fill Factor (채우기 비율)에 관한 오해와 진실 jevida(강성욱) 2016.09.15 3594
1742 LOB 데이터와 Shrink 작업 jevida(강성욱) 2016.09.15 1238
1741 데이터베이스 함수 검색 하기 jevida(강성욱) 2016.09.15 1063
1740 필터 통계 사용과 파리미터 사용 jevida(강성욱) 2016.09.15 1179
1739 유지관리 계획과 병렬처리 – Index Rebuild jevida(강성욱) 2016.09.15 1064
1738 유지관리 계획과 병렬 처리 – CHECKDB jevida(강성욱) 2016.09.15 1129
1737 쿼리 사이즈(길이) 에 따른 CPU 사용량 증가 jevida(강성욱) 2016.09.14 1301
1736 Ad-hoc 쿼리를 매개변수화 하여 성능 높이기 jevida(강성욱) 2016.09.14 1652
1735 통계 업데이트 옵션(ROWCOUNT and PAGECOUNT) jevida(강성욱) 2016.09.14 1228
1734 SQL Server Plan Guide 생성 및 사용 jevida(강성욱) 2016.09.14 1418
1733 SQL Server 그래픽 실행 계획 노드 정보 jevida(강성욱) 2016.09.14 1066
1732 프로파일러를 이용한 실행계획 캡처하기 jevida(강성욱) 2016.09.14 958
1731 SQL Server 그래픽 실행 계획 및 텍스트 실행 계획 jevida(강성욱) 2016.09.14 3104





XE Login