Функция динамического управления sys.dm_sql_referenced_entities (Transact-SQL)

Возвращает по одной строке для каждой определяемой сущности, на которую ссылается по имени определение указанной ссылающейся сущности в SQL Server. Зависимость между двумя сущностями создается в тех случаях, когда имя некоторой пользовательской сущности, называемой упоминаемой, встречается в составе постоянного выражения SQL в другой пользовательской сущности, называемой ссылающейся. Например, если в качестве ссылающейся сущности выступает хранимая процедура, то данная функция выводит список всех определяемых пользователем сущностей, упоминаемых в данной хранимой процедуре. К упоминаемым определяемым пользователем сущностям относятся: таблицы, представления, определяемые пользователем типы (UDT), а также другие хранимые процедуры.

Данная функция динамического управления может быть использована для отображения списка сущностей, упоминаемых заданной ссылающейся сущностью и имеющих следующие типы:

• сущности, привязанные к схеме;

• несвязанные сущности;

• межбазовые и межсерверные сущности;

• зависимости уровня столбца, как связанные, так и не связанные со схемами;

• определяемые пользователем типы (псевдонимы и типы CLR);

• коллекции схем XML

• функции секционирования

Синтаксис

sys.dm_sql_referenced_entities (
    ' [ schema_name. ] referencing_entity_name ' , ' <referencing_class> ' )

<referencing_class> ::=
{
    OBJECT
  | DATABASE_DDL_TRIGGER
  | SERVER_DDL_TRIGGER
}

Аргументы

• [ schema_name. ] referencing_entity_name
  Имя ссылающейся сущности. Если ссылающийся класс — OBJECT, указание schema_name обязательно.

  Аргумент schema_name.referencing_entity_name имеет тип nvarchar(517).

• <referencing_class> ::= { OBJECT | DATABASE_DDL_TRIGGER   | SERVER_DDL_TRIGGER }
  Класс заданной ссылающейся сущности. В одной инструкции может быть указан только один класс.

  Аргумент <referencing_class> имеет тип nvarchar(60).

Возвращаемая таблица

Исключения

Возвращает пустой результирующий набор, если выполняется любое из следующих условий.

• Указан системный объект.

• Указанная сущность не существует в текущей базе данных.

• Указанная сущность не ссылается ни на какие сущности.

• Передан недопустимый параметр.

Выдает ошибку, если заданная ссылающаяся сущность является пронумерованной хранимой процедурой.

Возвращает ошибку 2020, когда не удается разрешить зависимости столбца. Эта ошибка не препятствует возврату запросом зависимостей на уровне объектов.

Замечания

Данная функция может выполняться в контексте любой базы данных и осуществляет отображение списка сущностей, ссылающихся на триггер DDL уровня сервера.

В следующей таблице перечислены типы сущностей, для которых созданы и обновляются данные о зависимостях. Данные о зависимостях не создаются и не обновляются для правил, значений по умолчанию, временных таблиц, временных хранимых процедур и системных объектов.

* Таблица отслеживается в качестве ссылающейся сущности, только если она ссылается на модуль Transact-SQL, определяемый пользователем тип или коллекцию XML-схем в определении вычисляемого столбца, ограничении CHECK или ограничении DEFAULT.

** Пронумерованные хранимые процедуры с целочисленным значением больше 1 не отслеживаются в качестве ссылающихся или упоминаемых сущностей.

Разрешения

Требуется разрешение SELECT на представление sys.dm_sql_referenced_entities и разрешение VIEW DEFINITION на ссылающуюся сущность. Разрешение SELECT по умолчанию предоставляется роли public. Требует разрешения VIEW DEFINITION для базы данных, либо, если ссылающаяся сущность является триггером DDL уровня базы данных, разрешения ALTER DATABASE DDL TRIGGER. Если указанный модуль является триггером DDL уровня сервера, то требуется разрешение VIEW ANY DEFINITION на уровне сервера.

Примеры

А.Возврат списка сущностей, на которые ссылается триггер DDL уровня базы данных

В ходе выполнения следующего примера производится отображение списка сущностей (таблиц и столбцов), упоминаемых триггером DDL уровня базы данных ddlDatabaseTriggerLog.

USE AdventureWorks2012;
GO
SELECT referenced_schema_name, referenced_entity_name, referenced_minor_name, 
    referenced_minor_id, referenced_class_desc
FROM sys.dm_sql_referenced_entities ('ddlDatabaseTriggerLog', 'DATABASE_DDL_TRIGGER');
GO

Б.Возврат списка сущностей, упоминаемых объектом

В ходе выполнения следующего примера производится отображение списка сущностей, упоминаемых в определяемой пользователем функции dbo.ufnGetContactInformation.

USE AdventureWorks2012;
GO
SELECT referenced_schema_name, referenced_entity_name, referenced_minor_name, 
    referenced_minor_id, referenced_class_desc, is_caller_dependent, is_ambiguous
FROM sys.dm_sql_referenced_entities ('dbo.ufnGetContactInformation', 'OBJECT');
GO

В.Возврат списка зависимостей столбцов

В ходе выполнения представленного ниже примера осуществляется создание таблицы Table1, в которой содержится вычисляемый столбец c, определяемый как сумма столбцов a и b. Затем производится вызов представления sys.dm_sql_referenced_entities. Представление содержит две строки, по одной для каждого столбца, определенного в вычисляемом столбце.

USE AdventureWorks2012;
GO
CREATE TABLE dbo.Table1 (a int, b int, c AS a + b);
GO
SELECT referenced_schema_name AS schema_name,
    referenced_entity_name AS table_name,
    referenced_minor_name AS referenced_column,
    COALESCE(COL_NAME(OBJECT_ID(N'dbo.Table1'),referencing_minor_id), 'N/A') AS referencing_column_name
FROM sys.dm_sql_referenced_entities ('dbo.Table1', 'OBJECT');
GO
-- Remove the table.
DROP TABLE dbo.Table1;
GO

Ниже приводится результирующий набор.

schema_name table_name referenced_column referencing_column

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

dbo         Table1     a                 c

dbo         Table1     b                 c

Г.Возврат списка зависимостей столбцов, не привязанных к схеме

В ходе выполнения представленного ниже примера выполняется удаление таблицы Table1, а также создание таблицы Table2 и хранимой процедуры Proc1. Процедура ссылается на таблицу Table2 и на несуществующую таблицу Table1. Представление sys.dm_sql_referenced_entities запускается с помощью хранимой процедуры, которая указана в качестве ссылающейся сущности. В результирующем наборе показана одна строка для таблицы Table1 и 3 строки для таблицы Table2. Поскольку таблица Table1 не существует, то не удается разрешить зависимости столбца и возвращается ошибка 2020. Столбец is_all_columns_found возвращает 0 для таблицы Table1, указывая, что существуют столбцы, которые не удалось обнаружить.

USE AdventureWorks2012;
GO
IF OBJECT_ID ( 'dbo.Table1', 'U' ) IS NOT NULL 
    DROP TABLE dbo.Table1;
GO
CREATE TABLE dbo.Table2 (c1 int, c2 int);
GO
CREATE PROCEDURE dbo.Proc1 AS
    SELECT a, b, c FROM Table1;
    SELECT c1, c2 FROM Table2;
GO
SELECT referenced_id, referenced_entity_name AS table_name, referenced_minor_name AS referenced_column_name, is_all_columns_found
FROM sys.dm_sql_referenced_entities ('dbo.Proc1', 'OBJECT');
GO

Ниже приводится результирующий набор.

referenced_id table_name   referenced_column_name  is_all_columns_found

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

935674381     Table2       NULL                    1

935674381     Table2       C1                      1

935674381     Table2       C2                      1

NULL          Table1       NULL                    0

Msg 2020, Level 16, State 1, Line 1The dependencies reported for entity "dbo.Proc1" might not include references to all 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.

Д.Отображение параметров обслуживания динамических зависимостей

Следующий пример является продолжением примера Г и предназначен для отображения динамических зависимостей. Сначала в этом примере повторно создается таблица Table1, которая была удалена в примере Г. Затем снова выполняется sys.dm_sql_referenced_entities с использованием хранимой процедуры, которая указана в качестве ссылающейся сущности. Результирующий набор содержит обе таблицы и их соответствующие столбцы, определенные в хранимой процедуре. Кроме того, столбец is_all_columns_found возвращает значение 1 для всех объектов и столбцов.

USE AdventureWorks2012;
GO
CREATE TABLE Table1 (a int, b int, c AS a + b);
GO 
SELECT referenced_id, referenced_entity_name AS table_name, referenced_minor_name as column_name, is_all_columns_found
FROM sys.dm_sql_referenced_entities ('dbo.Proc1', 'OBJECT');
GO
DROP TABLE Table1, Table2;
DROP PROC Proc1;
GO

Ниже приводится результирующий набор.

referenced_id table_name   referenced_column_name  is_all_columns_found

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

935674381     Table2       NULL                    1

935674381     Table2       c1                      1

935674381     Table2       c2                      1

967674495     Table1       NULL                    1

967674495     Table1       a                       1

967674495     Table1       b                       1

967674495     Table1       c                       1

Е.Использование возвращения объекта или столбца

В следующем примере возвращаются зависимости объекта и столбца хранимой процедуры HumanResources.uspUpdateEmployeePersonalInfo. Данная процедура обновляет столбцы NationalIDNumber, BirthDate, MaritalStatus и Gender таблицы Employee на основании указанного значения BusinessEntityID. Другая хранимая процедура upsLogError определяется в блоке TRY…CATCH для отслеживания любых ошибок при выполнении. Столбцы is_selected, is_updated и is_select_all возвращают сведения о способе использования этих объектов и столбцов в рамках ссылающегося объекта. Измененная таблица и столбцы в обновленном столбце помечаются единицей. Единственный выбранный столбец — BusinessEntityID, а хранимая процедура uspLogError не выбрана и не изменена.

SELECT referenced_entity_name AS table_name, referenced_minor_name as column_name, is_selected, is_updated, is_select_all
FROM sys.dm_sql_referenced_entities ('HumanResources.uspUpdateEmployeePersonalInfo', 'OBJECT');

Ниже приводится результирующий набор.

table_name    column_name         is_selected is_updated is_select_all

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

uspLogError   NULL                0           0          0

Employee      NULL                0           1          0

Employee      BusinessEntityID    1           0          0

Employee      NationalIDNumber    0           1          0

Employee      BirthDate           0           1          0

Employee      MaritalStatus       0           1          0

Employee      Gender              0           1          0

См. также

Справочник

sys.dm_sql_referencing_entities (Transact-SQL)

Представление каталога sys.sql_expression_dependencies (Transact-SQL)