SQL 종속성 보고

sys.sql_expression_dependencies 카탈로그 뷰는 데이터베이스 소유자 또는 데이터베이스 관리자에게 지정한 데이터베이스에 대한 종속성 정보를 보고할 수 있는 기능을 제공합니다. 이 뷰를 사용하면 다음과 같은 전역적 질문에 답할 수 있습니다.

• 데이터베이스에 있는 서버 간 또는 데이터베이스 간 종속성은 무엇입니까?

• 데이터베이스 내에 있는 종속성은 무엇입니까?

• 호출자 종속 참조가 있는 데이터베이스의 엔터티는 무엇입니까?

• 데이터베이스의 엔터티에 종속된 서버 수준 또는 데이터베이스 수준 DDL 트리거는 무엇입니까?

• 데이터베이스의 모듈 중 UDT(사용자 정의 형식)를 사용하는 모듈은 무엇입니까?

sys.sql_expression_dependencies의 제한 사항은 다음과 같습니다.

• 서버 간 또는 데이터베이스 간 엔터티에 대한 종속성은 네 부분 또는 세 부분으로 된 올바른 이름을 지정할 경우에만 반환됩니다. 참조된 엔터티의 ID는 반환되지 않습니다.

• 열 수준 종속성은 스키마 바운드 엔터티에 대해서만 보고됩니다.

sys.dm_sql_referenced_entities 함수는 지정한 참조 엔터티의 정의에 이름으로 참조되는 각 사용자 정의 엔터티에 대해 하나의 행을 반환합니다. 참조 엔터티는 사용자 정의 개체, 서버 수준 DDL 트리거 또는 데이터베이스 수준 DDL 트리거일 수 있습니다. 이는 sys.sql_expression_dependencies에서 반환하는 정보와 동일하지만 결과 집합이 지정한 참조 엔터티에 참조되는 엔터티로 제한됩니다. 이 함수는 소유하는 모듈이나 VIEW DEFINITION 권한이 있는 모듈에 대한 종속성을 추적하려는 개발자에게 유용합니다.

sys.dm_sql_referencing_entities 함수는 다른 사용자 정의 엔터티를 이름으로 참조하는 현재 데이터베이스의 각 사용자 정의 엔터티에 대해 하나의 행을 반환합니다. 참조 엔터티는 사용자 정의 개체, 유형(별칭 또는 CLR UDT), XML 스키마 컬렉션 또는 파티션 함수일 수 있습니다. 이 함수는 소유하는 엔터티에 대한 종속성을 추적하려는 개발자에게 유용합니다. 예를 들어 개발자는 사용자 정의 형식을 수정하기 전에 이 함수를 사용하여 데이터베이스에서 해당 유형에 종속된 모든 엔터티를 확인할 수 있습니다. 테이블의 사용자 정의 형식에 대한 참조는 계산 열의 정의, CHECK 제약 조건 또는 DEFAULT 제약 조건에 해당 유형이 지정되어 있지 않은 한 보고되지 않습니다.

sys.sql_expression_dependencies 카탈로그 뷰 또는 sys.dm_sql_referenced_entities 동적 관리 함수를 쿼리하여 지정한 엔터티가 종속된 엔터티 목록을 반환할 수 있습니다. 예를 들어 저장 프로시저 또는 트리거와 같은 모듈에 참조되는 엔터티 목록을 반환할 수 있습니다.

다음 예에서는 하나의 테이블과 뷰 및 3개의 저장 프로시저를 만듭니다. 이러한 개체는 종속성 정보를 보고하는 방법을 보여 주기 위해 이후 쿼리에 사용됩니다. 여기서 MyView와 MyProc3은 모두 Mytable을 참조합니다. 또한 MyProc1은 MyView, MyProc2는 MyProc1을 참조합니다.

USE AdventureWorks2008R2;
GO
-- Create entities
CREATE TABLE dbo.MyTable (c1 int, c2 varchar(32));
GO
CREATE VIEW dbo.MyView
AS SELECT c1, c2 FROM dbo.MyTable;
GO
CREATE PROC dbo.MyProc1
AS SELECT c1 FROM dbo.MyView;
GO
CREATE PROC dbo.MyProc2
AS EXEC dbo.MyProc1;
GO
CREATE PROC dbo.MyProc3
AS SELECT * FROM AdventureWorks2008R2.dbo.MyTable;
   EXEC dbo.MyProc2;
GO

다음 예에서는 sys.sql_expression_dependencies 카탈로그 뷰를 쿼리하여 MyProc3에 참조되는 엔터티를 반환합니다.

USE AdventureWorks2008R2;
GO
SELECT OBJECT_NAME(referencing_id) AS referencing_entity_name 
    ,referenced_server_name AS server_name
    ,referenced_database_name AS database_name
    ,referenced_schema_name AS schema_name
    , referenced_entity_name
FROM sys.sql_expression_dependencies 
WHERE referencing_id = OBJECT_ID(N'dbo.MyProc3');
GO

결과 집합은 다음과 같습니다.

referencing_entity server_name database_name         schema_name referenced_entity

------------------ ----------- --------------------  ----------- -----------------

MyProc3            NULL        NULL                  dbo         MyProc2

MyProc3            NULL        AdventureWorks2008R2  dbo         MyTable

(2 row(s) affected)

MyProc3의 정의에 이름으로 참조되는 두 개의 엔터티가 반환됩니다. 서버 이름은 참조된 엔터티가 네 부분으로 된 올바른 이름을 사용하여 지정되지 않았으므로 NULL입니다. 데이터베이스 이름은 엔터티가 세 부분으로 된 올바른 이름을 사용하여 프로시저에 정의되었으므로 MyTable에 대해 표시됩니다.

sys.dm_sql_referenced_entities를 사용하여 유사한 정보를 반환할 수 있습니다. 이 함수는 개체 이름을 보고할 뿐만 아니라 스키마 바운드 엔터티와 비스키마 바운드 엔터티 모두에 대한 열 수준 종속성도 반환합니다. 다음 예에서는 MyProc3이 종속된 엔터티와 함께 열 수준 종속성을 반환합니다.

USE AdventureWorks2008R2;
GO
SELECT referenced_server_name AS server
    , referenced_database_name AS database_name
    , referenced_schema_name AS schema_name
    , referenced_entity_name AS referenced_entity
    , referenced_minor_name AS column_name
FROM sys.dm_sql_referenced_entities ('dbo.MyProc3', 'OBJECT');
GO

결과 집합은 다음과 같습니다.

server_name database_name         schema_name  referenced_entity  column_name

----------- --------------------  -----------  -----------------  -----------

NULL        NULL                  dbo          MyProc2            NULL

NULL        AdventureWorks2008R2  dbo          MyTable            NULL

NULL        AdventureWorks2008R2  dbo          MyTable            c1

NULL        AdventureWorks2008R2  dbo          MyTable            c2

(4 row(s) affected)

이 결과 집합에는 두 개의 동일한 엔터티가 반환되지만 MyTable의 열 c1 및 c2에 대한 종속성을 표시하는 두 개의 추가 행도 반환됩니다. 여기서 MyProc3의 정의에는 MyTable의 열을 참조하기 위해 SELECT * 문이 사용되었습니다. 이는 권장 코딩 방식은 아니지만 그래도 데이터베이스 엔진에서 열 수준 종속성이 추적됩니다.

지금까지 제공한 예에서는 엔터티가 직접 종속된 엔터티를 반환하는 방법을 보여 주었습니다. 다음 예에서는 재귀 CTE(공통 테이블 식)를 사용하여 엔터티에 대한 모든 직접 및 간접 종속성을 반환합니다.

DECLARE @referencing_entity AS sysname;
SET @referencing_entity = N'MyProc3';

WITH ObjectDepends(entity_name,referenced_schema, referenced_entity, referenced_id,level)
AS (
    SELECT entity_name = 
       CASE referencing_class
          WHEN 1 THEN OBJECT_NAME(referencing_id)
          WHEN 12 THEN (SELECT t.name FROM sys.triggers AS t 
                       WHERE t.object_id = sed.referencing_id)
          WHEN 13 THEN (SELECT st.name FROM sys.server_triggers AS st
                       WHERE st.object_id = sed.referencing_id) COLLATE database_default
       END
    ,referenced_schema_name
    ,referenced_entity_name
    ,referenced_id
    ,0 AS level 
    FROM sys.sql_expression_dependencies AS sed 
    WHERE OBJECT_NAME(referencing_id) = @referencing_entity 
UNION ALL
    SELECT entity_name = 
       CASE sed.referencing_class
          WHEN 1 THEN OBJECT_NAME(sed.referencing_id)
          WHEN 12 THEN (SELECT t.name FROM sys.triggers AS t 
                       WHERE t.object_id = sed.referencing_id)
          WHEN 13 THEN (SELECT st.name FROM sys.server_triggers AS st
                       WHERE st.object_id = sed.referencing_id) COLLATE database_default
       END
    ,sed.referenced_schema_name
    ,sed.referenced_entity_name
    ,sed.referenced_id
    ,level + 1   
    FROM ObjectDepends AS o
    JOIN sys.sql_expression_dependencies AS sed ON sed.referencing_id = o.referenced_id
    )
SELECT entity_name,referenced_schema, referenced_entity, level
FROM ObjectDepends
ORDER BY level;
GO

결과 집합은 다음과 같습니다.

entity_name  referenced_schema  referenced_entity  level

-----------  -----------------  -----------------  -----

MyProc3      dbo                MyProc2            0

MyProc3      dbo                MyTable            0

MyProc2      dbo                MyProc1            1

MyProc1      dbo                MyView             2

MyView       dbo                MyTable            3

(5 row(s) affected)

이 결과 집합에는 MyProc2 및 MyTable이 직접 종속성으로 반환됩니다(수준 0 값으로 표시됨). 세 번째 행에는 MyProc2의 정의에 참조되는 MyProc1에 대한 간접 종속성이 표시됩니다. 네 번째 행에는 MyProc1의 정의에 참조되는 MyView에 대한 종속성이 표시되며, 마지막으로 MyView의 정의에 참조되는 MyTable에 대한 종속성이 표시됩니다.

계층적 종속성 정보를 반환하면 지정한 엔터티에 대한 직접 및 간접 종속성의 전체 목록을 확인하고 이러한 개체를 다른 데이터베이스로 이동해야 할 경우 해당 개체의 배포 순서를 유추할 수 있습니다.

다음 예에서는 sys.dm_sql_referenced_entities 함수를 사용하여 동일한 계층적 종속성 정보를 반환합니다. MyProc3이 종속된 엔터티가 열 수준 종속성과 함께 반환됩니다.

USE AdventureWorks2008R2;
GO
DECLARE @entity AS sysname , @type AS sysname;
SET @entity = N'dbo.MyProc3';
SET @type = N'OBJECT';

WITH ObjectDepends(referenced_schema_name, referenced_entity_name, referenced_column, 
     referenced_id,level)
AS (
    SELECT 
     referenced_schema_name
    ,referenced_entity_name
    ,referenced_minor_name AS referenced_column
    ,referenced_id
    ,0 AS level 
    FROM sys.dm_sql_referenced_entities (@entity, @type)
    UNION ALL
    SELECT
     re.referenced_schema_name
    ,re.referenced_entity_name
    ,re.referenced_minor_name AS referenced_column
    ,re.referenced_id
    ,level + 1 
    FROM ObjectDepends AS o
    CROSS APPLY sys.dm_sql_referenced_entities (o.referenced_schema_name + N'.' + o.referenced_entity_name, @type) AS re
    )
SELECT referenced_schema_name, referenced_entity_name, referenced_column, level
FROM ObjectDepends
ORDER BY level;
GO

sys.sql_expression_dependencies 카탈로그 뷰 또는 sys.dm_sql_referencing_entities 동적 관리 함수를 쿼리하여 지정한 엔터티에 종속된 엔터티 목록을 반환할 수 있습니다. 예를 들어 지정한 엔터티가 테이블인 경우 해당 정의에서 이 테이블을 이름으로 참조하는 모든 엔터티가 반환됩니다.

다음 예에서는 엔터티 dbo.MyTable을 참조하는 엔터티를 반환합니다.

USE AdventureWorks2008R2;
GO
SELECT OBJECT_SCHEMA_NAME ( referencing_id ) AS referencing_schema_name,
    OBJECT_NAME(referencing_id) AS referencing_entity_name, 
    COALESCE(COL_NAME(referencing_id, referencing_minor_id), '(n/a)') AS referencing_column, 
    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,
    is_caller_dependent, is_ambiguous
FROM sys.sql_expression_dependencies AS sed
WHERE referenced_id = OBJECT_ID(N'dbo.MyTable');
GO

sys.dm_sql_referenced_entities 동적 관리 함수를 사용하여 유사한 정보를 반환할 수 있습니다.

USE AdventureWorks2008R2;
GO
SELECT referencing_schema_name, referencing_entity_name, referencing_id, referencing_class_desc, is_caller_dependent
FROM sys.dm_sql_referencing_entities ('dbo.MyTable', 'OBJECT');
GO

열 수준 종속성은 스키마 바운드 엔터티와 비스키마 바운드 엔터티 모두에 대해 sys.dm_sql_referenced_entities를 사용하여 보고할 수 있습니다. 스키마 바운드 엔터티에 대한 열 수준 종속성은 sys.sql_expression_dependencies를 사용하여 보고할 수도 있습니다.

다음 예에서는 sys.dm_sql_referenced_entities를 쿼리하여 비스키마 바운드 엔터티에 대한 열 수준 종속성을 보고합니다. 이 예에서는 먼저 Table1 및 Table 2와 저장 프로시저 Proc1을 만듭니다. 이 프로시저는 Table1의 b 및 c 열과 Table2의 c2 열을 참조합니다. 뷰 sys.dm_sql_referenced_entities는 참조 엔터티로 지정된 저장 프로시저와 함께 실행됩니다. 결과 집합에는 참조된 엔터티 Table1 및 Table2에 대한 행과 저장 프로시저의 정의에서 참조되는 열에 대한 행이 포함됩니다. NULL은 테이블을 참조하는 행의 column_name 열에 반환됩니다.

USE AdventureWorks2008R2;
GO
CREATE TABLE dbo.Table1 (a int, b int, c int);
GO
CREATE TABLE dbo.Table2 (c1 int, c2 int);
GO
CREATE PROCEDURE dbo.Proc1 AS
    SELECT b, c FROM dbo.Table1;
    SELECT c2 FROM dbo.Table2;
GO
SELECT referenced_id, referenced_entity_name AS table_name, referenced_minor_name AS column_name
FROM sys.dm_sql_referenced_entities ('dbo.Proc1', 'OBJECT');
GO

결과 집합은 다음과 같습니다.

referenced_id, table_name,  column_name

-------------  -----------  -------------

151671588      Table1       NULL

151671588      Table1       b

151671588      Table1       c

2707154552     Table2       NULL

2707154552     Table2       c2

데이터베이스 간 종속성은 엔터티가 세 부분으로 된 올바른 이름을 사용하여 다른 엔터티를 참조할 때 생성됩니다. 서버 간 참조는 엔터티가 네 부분으로 된 올바른 이름을 사용하여 다른 엔터티를 참조할 때 생성됩니다. 서버 및 데이터베이스의 이름은 해당 이름을 명시적으로 지정할 경우에만 기록됩니다. 예를 들어 MyServer.MyDB.MySchema.MyTable로 지정된 경우 서버 및 데이터베이스 이름이 기록되지만 MyServer..MySchema.MyTable로 지정된 경우에는 서버 이름만 기록됩니다. 서버 간 및 데이터베이스 간 종속성을 추적하는 방법은 SQL 종속성 이해를 참조하십시오.

데이터베이스 간 및 서버 간 종속성은 sys.sql_expression_dependencies 또는 sys.dm_sql_referenced_entitites를 사용하여 보고할 수 있습니다.

다음 예에서는 모든 데이터베이스 간 종속성을 반환합니다. 예에서는 먼저 db1 데이터베이스를 만들고 db2 및 db3 데이터베이스의 테이블을 참조하는 두 개의 저장 프로시저를 만듭니다. 그런 다음 sys.sql_expression_dependencies 테이블을 쿼리하여 프로시저와 테이블 사이의 데이터베이스 간 종속성을 보고합니다. 프로시저의 정의에 참조된 엔터티 t3에 대한 스키마 이름이 지정되지 않았으므로 해당 엔터티에 대한 referenced_schema_name 열에 NULL이 반환됩니다.

CREATE DATABASE db1;
GO
USE db1;
GO
CREATE PROCEDURE p1 AS SELECT * FROM db2.s1.t1;
GO
CREATE PROCEDURE p2 AS
    UPDATE db3..t3
    SET c1 = c1 + 1;
GO
SELECT OBJECT_NAME (referencing_id),referenced_database_name, 
    referenced_schema_name, referenced_entity_name
FROM sys.sql_expression_dependencies
WHERE referenced_database_name IS NOT NULL;
GO
USE master;
GO
DROP DATABASE db1;
GO

호출자 종속 참조는 참조된 엔터티의 스키마 바인딩이 런타임에 발생하므로 엔터티 ID 확인이 호출자의 기본 스키마에 종속됨을 의미합니다. 이를 흔히 동적 스키마 바인딩이라고 하며 이 작업은 참조된 엔터티가 스키마 이름 지정 없이 EXECUTE 문으로 호출된 저장 프로시저, 확장 저장 프로시저 또는 비스키마 바운드 사용자 정의 함수인 경우 발생합니다. 예를 들어 EXECUTE MySchema.MyProc 형식의 엔터티에 대한 참조는 호출자 종속 참조가 아니지만 EXECUTE MyProc 형식의 참조는 호출자 종속 참조입니다.

호출자 종속 참조가 발생하는 모듈 실행 시 예기치 않은 동작이 발생할 수 있습니다. 예를 들어 한 부분으로 된 이름을 사용하여 프로시저를 참조하는 다음 저장 프로시저를 살펴보십시오.

CREATE PROCEDURE dbo.Proc1
AS EXECUTE dbo.Proc2;
GO

Proc1 실행 시 Proc2는 호출자의 스키마에 바인딩됩니다. Proc1을 S1의 기본 스키마를 보유하는 User1이 실행하고 S2의 기본 스키마를 보유하는 User2가 실행한다고 가정할 때 User1이 Proc1을 실행하면 참조된 엔터티가 S1.Proc2로 확인되고 User2가 Proc1을 실행하면 참조된 엔터티가 S2.Proc2로 확인됩니다. 이러한 동작으로 인해 Proc2의 ID를 Proc1이 실행될 때까지 확인할 수 없으며 이에 따라 is_caller_dependent 열이 sys.sql_expression_dependencies 뷰 및 sys.dm_sql_referenced_entities 함수에서 1로 설정됩니다. Proc1 실행 시 데이터베이스 엔진은 호출자의 기본 스키마에서 참조된 엔터티 Proc2를 찾습니다. 해당 엔터티를 찾을 수 없으면 dbo 스키마를 검사합니다. dbo 스키마에서 Proc2를 찾을 수 없으면 Proc2의 ID를 확인할 수 없으며 문이 실패합니다. 데이터베이스 엔터티를 참조할 때는 잠재적 응용 프로그램 오류를 방지하기 위해 두 부분으로 된 이름을 지정하는 것이 좋습니다.

다음 예에서는 호출자 종속 참조가 들어 있는 현재 데이터베이스의 각 엔터티를 반환합니다.

SELECT OBJECT_NAME(referencing_id) AS referencing_entity, referenced_database_name, 
    referenced_schema_name, referenced_entity_name, referenced_id 
FROM sys.sql_expression_dependencies
WHERE is_caller_dependent = 1;

다음 예에서는 해당 정의에서 지정한 유형을 참조하는 현재 데이터베이스의 각 엔터티를 반환합니다. 결과 집합에는 두 개의 저장 프로시저가 이 유형을 사용함이 표시됩니다. 해당 유형은 HumanResources.Employee 테이블에 있는 여러 열의 정의에도 사용되지만 테이블에 있는 계산 열의 정의, CHECK 제약 조건 또는 DEFAULT 제약 조건에 없으므로 해당 테이블에 대해 아무 행도 반환되지 않습니다.

USE AdventureWorks2008R2;
GO
SELECT referencing_schema_name, referencing_entity_name, referencing_id, referencing_class_desc, is_caller_dependent
FROM sys.dm_sql_referencing_entities ('dbo.Flag', 'TYPE');
GO

서버 수준 DDL 트리거 종속성은 컨텍스트가 master 데이터베이스로 설정되어 있는 경우에만 sys.sql_expression_dependencies 및 sys.dm_sql_referencing_entities를 사용하여 보고할 수 있습니다. sys.dm_sql_referenced_entities 함수를 사용할 경우에는 어떤 데이터베이스 컨텍스트이든 상관 없습니다.

다음 예에서는 sys.sql_expression_dependencies 뷰를 쿼리하여 서버 수준 DDL 트리거 종속성을 보고합니다.

USE master;
GO
SELECT OBJECT_NAME(referencing_id) AS referencing_entity, referencing_class_desc, referenced_server_name, referenced_database_name, referenced_schema_name, referenced_entity_name, referenced_id
FROM sys.sql_expression_dependencies
WHERE referencing_class = 13;