Signalement des dépendances SQL

L'affichage catalogue sys.sql_expression_dependencies permet au propriétaire ou à l'administrateur de base de données de signaler des informations de dépendance pour une base de données spécifiée. Cet affichage permet de répondre aux questions globales, telles que :

• Quelles sont les dépendances entre serveurs ou les bases de données croisées dont la base de données dispose ?

• Quelles sont les dépendances qui existent au sein de la base de données ?

• Quelles sont les entités de la base de données qui disposent de références dépendant de l'appelant ?

• Quels sont les déclencheurs DDL au niveau du serveur ou au niveau de la base de données qui ont des dépendances sur les entités de la base de données ?

• Quels sont les modules de la base de données qui utilisent un type défini par l'utilisateur (UDT) ?

sys.sql_expression_dependencies présente les limitations suivantes :

• Les dépendances sur les entités des bases de données croisées et entre serveurs sont retournées uniquement lorsqu'un nom en trois ou quatre parties valide est spécifié. Les ID pour les entités référencées ne sont pas retournés.

• Les dépendances au niveau des colonnes sont uniquement signalées pour les entités liées au schéma.

La fonction sys.dm_sql_referenced_entities retourne une ligne pour chaque entité définie par l'utilisateur référencée par son nom dans la définition de l'entité de référence spécifiée. L'entité de référence peut être un objet défini par l'utilisateur, un déclencheur DDL au niveau du serveur ou un déclencheur DDL au niveau de la base de données. Il s'agit des mêmes informations qui sont retournées par sys.sql_expression_dependencies. Toutefois, le jeu de résultats est limité aux entités qui sont référencées par l'entité de référence spécifiée. Cette fonction est utile pour les développeurs qui veulent suivre les dépendances sur les modules qu'ils possèdent ou sur ceux pour lesquels ils ont l'autorisation VIEW DEFINITION.

La fonction sys.dm_sql_referencing_entities retourne une ligne pour chaque entité définie par l'utilisateur dans la base de données actuelle qui référence une autre entité définie par l'utilisateur par son nom. L'entité de référence peut être un objet défini par l'utilisateur, un type (alias ou CLR UDT), une collection de schémas XML ou une fonction de partition. Cette fonction est utile pour les développeurs qui veulent suivre les dépendances sur les entités qu'ils possèdent. Par exemple, avant de modifier un type défini par l'utilisateur, un développeur peut utiliser cette fonction pour déterminer toutes les entités de la base de données qui dépendent de ce type. Notez que les références à un type défini par l'utilisateur dans une table ne sont pas signalées à moins que le type soit spécifié dans la définition d'une colonne calculée, d'une contrainte CHECK ou d'une contrainte DEFAULT.

Vous pouvez interroger l'affichage catalogue sys.sql_expression_dependencies ou la fonction de gestion dynamique sys.dm_sql_referenced_entities pour retourner une liste des entités dont dépend une entité spécifiée. Par exemple, vous pouvez retourner une liste des entités qui sont référencées par un module tel qu'une procédure stockée ou un déclencheur.

L'exemple suivant crée une table, une vue et trois procédures stockées. Ces objets sont utilisés dans les requêtes ultérieures pour montrer comment signaler des informations de dépendance. Observez que MyView et MyProc3 référencent tous les deux Mytable. MyProc1 référence MyView et MyProc2 référence 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

L'exemple suivant interroge l'affichage catalogue sys.sql_expression_dependencies pour retourner les entités qui sont référencées par 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

Voici l'ensemble des résultats.

referencing_entity server_name database_name         schema_name referenced_entity

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

MyProc3            NULL        NULL                  dbo         MyProc2

MyProc3            NULL        AdventureWorks2008R2  dbo         MyTable

(2 ligne(s) affectée(s))

Les deux entités référencées par nom dans la définition de MyProc3 sont retournées. Le nom du serveur est NULL parce que les entités référencées ne sont pas spécifiées à l'aide d'un nom en quatre parties valide. Le nom de la base de données est affiché pour MyTable parce que l'entité a été définie dans la procédure à l'aide d'un nom en trois parties valide.

Des informations semblables peuvent être retournées en utilisant sys.dm_sql_referenced_entities. En plus du signalement des noms d'objets, cette fonction peut être utilisée pour retourner des dépendances au niveau des colonnes sur des entités liées au schéma et non liées au schéma. L'exemple suivant retourne les entités dont MyProc3 dépend, notamment les dépendances au niveau des colonnes.

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

Voici l'ensemble des résultats.

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 ligne(s) affectée(s))

Dans ce jeu de résultats, les deux mêmes entités sont retournées. Toutefois, deux lignes supplémentaires sont retournées montrant la dépendance sur les colonnes c1 et c2 dans MyTable. Remarquez que dans la définition de MyProc3, une instruction SELECT * a été utilisée pour référencer les colonnes dans MyTable. Cette méthode de codage n'est pas recommandée. Toutefois, les dépendances au niveau des colonnes sont encore suivies par Moteur de base de données.

Jusqu'à présent, les exemples ont illustré comment retourner les entités dont une entité dépend directement. L'exemple suivant utilise une expression de table commune récursive (CTE) pour retourner toutes les dépendances directes et indirectes sur une entité.

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

Voici l'ensemble des résultats.

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 ligne(s) affectée(s))

Dans ce jeu de résultats, MyProc2 et MyTable sont retournés en tant que dépendances directes, tel qu'indiqué par la valeur de niveau 0. La troisième ligne affiche la dépendance indirecte sur MyProc1 qui est référencé dans la définition de MyProc2. La quatrième ligne affiche la dépendance sur MyView qui est référencé dans la définition de MyProc1 et enfin, une dépendance sur MyTable qui est référencé dans la définition de MyView.

En retournant des informations de dépendance hiérarchiques, vous pouvez déterminer la liste complète des dépendances directes et indirectes sur une entité donnée et déduire l'ordre dans lequel ces objets doivent être déployés s'ils doivent être déplacés dans une autre base de données.

L'exemple suivant retourne les mêmes informations de dépendance hiérarchiques à l'aide de la fonction sys.dm_sql_referenced_entities. Les entités dont MyProc3 dépend sont retournées, notamment les dépendances au niveau des colonnes.

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

Vous pouvez interroger l'affichage catalogue sys.sql_expression_dependencies ou la fonction de gestion dynamique sys.dm_sql_referencing_entities pour retourner une liste des entités qui dépendent de l'entité spécifiée. Par exemple, si l'entité spécifiée est une table, toutes les entités qui référencent cette table par son nom dans leur définition sont retournées.

L'exemple suivant retourne les entités qui référencent l'entité 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

Des informations semblables peuvent être retournées à l'aide de la fonction de gestion dynamique 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

Les dépendances au niveau des colonnes peuvent être signalées à l'aide de sys.dm_sql_referenced_entities pour les entités liées au schéma et celles non liées au schéma. Les dépendances au niveau des colonnes sur les entités liées au schéma peuvent également être signalées à l'aide de sys.sql_expression_dependencies.

L'exemple suivant interroge sys.dm_sql_referenced_entities pour signaler des dépendances au niveau des colonnes sur les entités non liées au schéma. L'exemple crée d'abord Table1 et Table 2 et la procédure stockée Proc1. La procédure référence les colonnes b et c dans Table1 et la colonne c2 dans Table2. 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 contient des lignes pour les entités référencées Table1 et Table2 et les colonnes qui sont référencées dans la définition de la procédure stockée. Notez que la valeur Null est retournée dans la colonne column_name, sur les lignes qui référencent les tables.

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

Voici l'ensemble des résultats.

referenced_id, table_name,  column_name

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

151671588      Table1       NULL

151671588      Table1       b

151671588      Table1       c

2707154552     Table2       NULL

2707154552     Table2       c2

Une dépendance de bases de données croisées est créée lorsqu'une entité fait référence à une autre entité en utilisant un nom en trois parties valide. Une référence entre serveurs est créée lorsqu'une entité fait référence à une autre entité en utilisant un nom en quatre parties valide. Le nom du serveur et de la base de données est enregistré uniquement lorsque le nom est explicitement spécifié. Par exemple, lorsque MyServer.MyDB.MySchema.MyTable est spécifié, les noms du serveur et de la base de données sont enregistrés. Toutefois, lorsque MyServer..MySchema.MyTable est spécifié, seul le nom du serveur est enregistré. Pour plus d'informations sur le suivi des dépendances entre serveurs et de bases de données croisées, consultez Description des dépendances SQL.

Les dépendances entre serveurs et de bases de données croisées peuvent être signalées à l'aide de sys.sql_expression_dependencies ou sys.dm_sql_referenced_entitites.

L'exemple suivant retourne toutes les dépendances de bases de données croisées. L'exemple crée d'abord la base de données db1 et deux procédures stockées qui référencent les tables dans les bases de données db2 et db3. La table sys.sql_expression_dependencies est alors interrogée pour signaler les dépendances de bases de données croisées entre les procédures et les tables. Remarquez que la valeur Null est retournée dans la colonne referenced_schema_name pour l'entité référencée t3, car aucun nom de schéma n'a été spécifié pour cette entité dans la définition de la procédure.

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

Une référence dépendant de l'appelant signifie que la liaison de schéma de l'entité référencée se produit au moment de l'exécution. Par conséquent, la résolution de l'ID d'entité dépend du schéma par défaut de l'appelant. Elle est généralement appelée liaison de schéma dynamique et se produit lorsque l'entité référencée est une procédure stockée, une procédure stockée étendue ou une fonction définie par l'utilisateur non liée au schéma appelée dans une instruction EXECUTE sans spécifier un nom de schéma. Par exemple, une référence à une entité au format EXECUTE MySchema.MyProc ne dépend pas de l'appelant, mais une référence au format EXECUTE MyProc dépend de l'appelant.

Les références dépendant de l'appelant peuvent provoquer un comportement inattendu lorsque le module dans lequel il est référencé est exécuté. Par exemple, considérons la procédure stockée suivante qui référence une procédure en utilisant un nom en une partie.

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

Lorsque Proc1 est exécutée, Proc2 effectue une liaison au schéma de l'appelant. Supposons que Proc1 est exécutée par User1 qui a un schéma par défaut de S1 et par User2 qui a un schéma par défaut de S2. Lorsque Proc1 est exécuté par User1, l'entité référencée est résolue en S1.Proc2. Lorsque Proc1 est exécuté par User2, l'entité référencée est résolue en S2.Proc2. En raison de ce comportement, l'ID de Proc2 ne peut pas être résolu tant que Proc1 n'est pas exécutée. Par conséquent, la colonne is_caller_dependent a la valeur 1 dans l'affichage sys.sql_expression_dependencies et la fonction sys.dm_sql_referenced_entities. Lorsque Proc1 est exécuté, le Moteur de base de données recherchera l'entité Proc2 référencée dans le schéma par défaut de l'appelant. Si elle ne peut pas être trouvée, le schéma dbo est vérifié. Si l'entité Proc2 ne peut pas être trouvée dans le schéma dbo, l'ID de Proc2 ne peut pas être résolu et l'instruction échoue. Nous recommandons de spécifier des noms en deux parties lors du référencement d'entités de base de données afin d'éliminer des erreurs d'application potentielles.

L'exemple suivant retourne chaque entité dans la base de données actuelle qui contient une référence dépendant de l'appelant.

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;

L'exemple suivant retourne chaque entité dans la base de données actuelle qui référence le type spécifié dans sa définition. Le jeu de résultats montre que deux procédures stockées utilisent ce type. Le type est également utilisé dans la définition de plusieurs colonnes dans la table HumanResources.Employee. Toutefois, étant donné que le type n'est pas dans la définition d'une colonne calculée, d'une contrainte CHECK ou d'une contrainte DEFAULT dans la table, aucune ligne n'est retournée pour la table.

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

Vous pouvez faire un rapport sur les dépendances de déclencheurs DDL au niveau du serveur en utilisant sys.sql_expression_dependencies et sys.dm_sql_referencing_entities uniquement lorsque le contexte est défini sur la base de données master. Lors de l'utilisation de la fonction sys.dm_sql_referenced_entities, le contexte peut être toute base de données.

L'exemple suivant interroge l'affichage sys.sql_expression_dependenciespour faire un rapport sur les dépendances de déclencheurs DDL au niveau du serveur.

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;