sys.dm_sql_referenced_entities (Transact-SQL)

Retourne une ligne pour chaque entité définie par l'utilisateur référencée par nom dans la définition de l'entité de référence spécifiée. Une dépendance entre deux entités est créée lorsqu'une entité définie par l'utilisateur, appelée entité référencée, apparaît par nom dans une expression SQL rendue persistante d'une autre entité définie par l'utilisateur, appelée entité de référence. Par exemple, si une procédure stockée est l'entité de référence spécifiée, cette fonction retourne toutes les entités définies par l'utilisateur qui sont référencées dans la procédure stockée, telles que les tables, vues, types définis par l'utilisateur ou autres procédures stockées.

Vous pouvez utiliser cette fonction de gestion dynamique pour établir des rapports sur les types suivants d'entités référencées par l'entité de référence indiquée :

• Entités liées au schéma

• Entités non liées au schéma

• Entités des bases de données croisées et entre serveurs

• Dépendances au niveau des colonnes sur les entités liées au schéma et non liées au schéma

• Types définis par l'utilisateur (alias et CLR)

• Collections de schémas XML

• Fonctions de partition

Syntaxe

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

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

Arguments

• [ schema_name. ] referencing_entity_name
  Nom de l'entité de référence. schema_name est requis lorsque la classe de référence est OBJECT.

  schema_name.referencing_entity_name est de type nvarchar(517).

• <referencing_class> ::= { OBJECT | DATABASE_DDL_TRIGGER   | SERVER_DDL_TRIGGER }
  Classe de l'entité de référence spécifiée. Une seule classe peut être spécifiée par instruction.

  <referencing_class> est de type nvarchar(60).

Table retournée

Exceptions

Retourne un jeu de résultats vide sous chacune des conditions suivantes :

• Un objet système est spécifié.

• L'entité spécifiée n'existe pas dans la base de données active.

• L'entité spécifiée ne référence pas d'autres entités.

• Un paramètre non valide est passé.

Retourne une erreur lorsque l'entité de référence spécifiée est une procédure stockée numérotée.

Retourne l'erreur 2020 lorsque des dépendances de colonnes ne peuvent pas être résolues. Cette erreur n'empêche pas la requête de retourner des dépendances de niveau objet.

Notes

Cette fonction peut être exécutée dans le contexte de n'importe quelle base de données pour retourner les entités qui référencent un déclencheur DDL au niveau du serveur.

Le tableau suivant répertorie les types des entités pour lesquelles les informations de dépendance sont créées et gérées. Les informations de dépendance ne sont pas créées ni gérées pour les règles, les valeurs par défaut, les tables temporaires, les procédures stockées temporaires ou les objets système.

* Une table est suivie comme entité de référence uniquement lorsqu'elle fait référence à un module Transact-SQL, un type défini par l'utilisateur ou une collection de schémas XML dans la définition d'une colonne calculée, d'une contrainte CHECK ou d'une contrainte DEFAULT.

** Les procédures stockées numérotées avec une valeur entière supérieure à 1 ne sont pas suivies en tant qu'entité de référence ou référencée.

Autorisations

Requiert l'autorisation SELECT sur sys.dm_sql_referenced_entities et l'autorisation VIEW DEFINITION sur l'entité de référence. Par défaut, l'autorisation SELECT est accordée à public. Requiert l'autorisation VIEW DEFINITION sur la base de données ou l'autorisation ALTER DATABASE DDL TRIGGER sur la base de données lorsque l'entité de référence est un déclencheur DDL au niveau de la base de données. Requiert l'autorisation VIEW ANY DEFINITION sur le serveur lorsque l'entité de référence est un déclencheur DDL au niveau du serveur.

Exemples

A.Retour d'entités qui sont référencées par un déclencheur DDL au niveau de la base de données

L'exemple suivant retourne les entités (tables et colonnes) qui sont référencées par le déclencheur DDL au niveau de la base de données 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

B.Retour d'entités qui sont référencées par un objet

L'exemple suivant retourne les entités qui sont référencées par la fonction définie par l'utilisateur 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

C.Retour de dépendances de colonnes

L'exemple suivant crée la table Table1 avec la colonne calculée c définie comme étant la somme des colonnes a et b. La vue sys.dm_sql_referenced_entities est ensuite appelée. La vue retourne deux lignes, une pour chaque colonne définie dans la colonne calculée.

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

Voici l'ensemble des résultats.

schema_name table_name referenced_column referencing_column

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

dbo         Table1     a                 c

dbo         Table1     b                 c

D.Retour de dépendances de colonnes non liées au schéma

L'exemple suivant supprime Table1, puis crée Table2 et la procédure stockée Proc1. La procédure référence Table2 et la table Table1 inexistante. La vue sys.dm_sql_referenced_entities est exécutée avec la procédure stockée spécifiée comme entité de référence. Le jeu de résultats montre une ligne pour Table1 et 3 lignes pour Table2. Étant donné que Table1 n'existe pas, les dépendances de colonnes ne peuvent pas être résolues et l'erreur 2020 est retournée. La colonne is_all_columns_found retourne 0 pour Table1 indiquant qu'il y a des colonnes qui ne peuvent pas être découvertes.

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

Voici l'ensemble des résultats.

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.

E.Illustration de la maintenance de dépendance dynamique

L'exemple suivant étend l'exemple D pour montrer que les dépendances sont maintenues dynamiquement. L'exemple recrée d'abord Table1, qui a été supprimée dans l'exemple D. Ensuite, la vue sys.dm_sql_referenced_entities est réexécutée avec la procédure stockée spécifiée comme entité de référence. Le jeu de résultats indique que les tables et leurs colonnes respectives définies dans la procédure stockée sont retournées. En outre, la colonne is_all_columns_found retourne 1 pour tous les objets et colonnes.

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

Voici l'ensemble des résultats.

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

F.Obtenir l'utilisation de l'objet ou de la colonne

L'exemple suivant permet d'obtenir les dépendances d'objet et de colonne de la procédure stockée HumanResources.uspUpdateEmployeePersonalInfo. Cette procédure met à jour les colonnes NationalIDNumber, BirthDate, MaritalStatus et Gender de la table Employee en fonction d'une valeur spécifiée BusinessEntityID. Une autre procédure stockée, upsLogError est définie dans un bloc TRY… CATCH pour capturer les erreurs d'exécution. Les colonnes is_selected, is_updated et is_select_all retournent des informations sur la façon dont ces objets et colonnes sont utilisés dans l'objet de référence. La table et les colonnes qui sont modifiées sont indiquées par un 1 dans la colonne is_updated. Seule la colonne BusinessEntityID est sélectionnée et la procédure stockée uspLogError n'est ni sélectionnée ni modifiée.

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');

Voici l'ensemble des résultats.

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

Voir aussi

Référence

sys.dm_sql_referencing_entities (Transact-SQL)

sys.sql_expression_dependencies (Transact-SQL)