MS-SQL Server

MSSQL2008 개체 종속성 식별

고희수 2010. 11. 22. 10:27

비지니스에 대한 요구사항을 수용할 준비는 언제든 돼있어야 하고,
그에 대한 영향도분석에 있어서 첫번째 절차는 아마도 이런게 아닐까 생각됩니다.

요구사항 혹은 구조적인 문제로 스키마가 변경돼야 할때,
변경해야할 스키마의 종속개체들을 확인하고 종속개체들까지 같이 수정해야 합니다.

 이를 위해 SQL2008에서 종속개체 확인을 위한 DMF(Dynamic Management Functions) 를 지원합니다.

sys.dm_sql_referenced_entities
확인가능한 참조유형
 - 스키마 바운드 또는 비스키마 바운드 엔터티
 - 데이터베이스 수준 DDL 트리거
 - 서버 수준 DDL 트리거

sys.dm_sql_referencing_entities
확인가능한 참조유형
 - 스키마 바운드 또는 비스키마 바운드 엔터티
 - 데이터베이스 수준 DDL 트리거
 - 서버 수준 DDL 트리거

sys.sql_expression_dependencies
확인가능한 참조유형
 - 스키마 바운드 엔터티
 - 비스키마 바운드 엔터티
 - 데이터베이스 간 및 서버 간 엔터티. 엔터티 이름이 보고되지만
   엔터티 ID는 확인되지 않습니다.
 - 스키마 바운드 엔터티에 대한 열 수준 종속성. 비스키마 바운드 개체에 대한
   열 수준 종속성은 sys.dm_sql_referenced_entities를 사용하여 반환할 수 있습니다.
 - master 데이터베이스의 컨텍스트에서 서버 수준 DLL 트리거

 SQL2008이전에서는 종속성에 대한 식별을 개체ID를 사용해서 추적했는데요,
SQL2008부터는 개체명을 사용해 추적한다고 합니다. 그로 인해
개체명 식별로 인해 삭제된 이후에도 추적이 가능하며
데이터베이스에 아직 생성이 돼있는지에 대한 추적도 가능하다고 합니다.

동적쿼리 포함개체 및 다른 데이터베이스 개체에 대한 참조정보 식별시 제한이 좀 있네요.

 /* Create Employee Table */
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Employee]') AND type in (N'U'))
DROP TABLE [dbo].[Employee]
GO

CREATE TABLE [dbo].[Employee]
(
[Emp_ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
[Last_Name] [nvarchar](50) NULL,
[First_Name] [nvarchar](50) NULL,
[Age] [int] NULL,
)
GO

 /* Create Stored Procedure "usp_GetEmployeeName" to return Employee Name */
CREATE PROCEDURE dbo.usp_GetEmployeeName
AS
BEGIN
SELECT Last_Name + ' ' + First_Name AS EmployeeName
FROM dbo.Employee
END
GO

/* Find all object which are referencing to "Employee" table */
SELECT
referencing_schema_name +'.'+ referencing_entity_name AS ReferencedEntityName,
referencing_class_desc AS ReferencingEntityDescription
FROM sys.dm_sql_referencing_entities ('dbo.Employee', 'OBJECT');
GO

 /* Find all object which are referenced by "usp_GetEmployeeName" stored procedure */
SELECT
referenced_schema_name +'.'+ referenced_entity_name AS ReferencedEntityName,
referenced_minor_name AS ReferencedMinorName
FROM sys.dm_sql_referenced_entities ('dbo.usp_GetEmployeeName', 'OBJECT');
GO
 
 /* Identifying Object Dependencies */
SELECT
SCHEMA_NAME(O.SCHEMA_ID) +'.'+ o.name AS ReferencingObject,
SED.referenced_schema_name +'.'+SED.referenced_entity_name AS ReferencedObject
FROM sys.all_objects O INNER JOIN sys.sql_expression_dependencies SED
ON O.OBJECT_ID=SED.REFERENCING_ID
WHERE O.name = 'usp_GetEmployeeName'
GO
 
참고: http://www.mssqltips.com/tip.asp?tip=1768