Signalement des dépendances SQL
Les dépendances SQL correspondent aux références par nom utilisées dans les expressions SQL qui rendent une entité définie par l'utilisateur dépendante d'une autre entité. Par exemple, les vues et les procédures stockées dépendent de l'existence de tables qui contiennent les données retournées par la vue ou la procédure. Le signalement des informations de dépendance est utile dans les scénarios suivants :
Déplacement d'un module tel une procédure stockée d'une application à une autre.
Avant de déplacer un module, vous pouvez déterminer s'il existe des entités de base de données ou de base de données croisées référencées par le module qui doivent également être déplacées avec le module.
Modification de la définition d'une entité, telle que l'ajout ou la suppression d'une colonne dans une table.
Avant de modifier une entité, vous pouvez déterminer s'il existe d'autres entités qui dépendent de la définition actuelle de l'entité. Ces entités dépendantes peuvent produire des résultats inattendus lorsqu'elles sont interrogées ou appelées après la modification. Elles peuvent nécessiter une opération d'actualisation des métadonnées ou une modification de leur définition.
Déplacement d'une ou de plusieurs bases de données d'un serveur à un autre.
Avant de déplacer des bases de données sur un autre serveur, vous pouvez déterminer les entités d'une base de données qui ont des dépendances sur les entités dans une autre base de données. Ainsi, vous savez qu'il faut déplacer ces bases de données sur le même serveur.
Configuration du basculement pour les applications qui englobent plusieurs bases de données.
Vous voulez vous assurer que votre application est disponible à tous moments et qu'elle utilise la mise en miroir de bases de données comme stratégie de basculement. L'application dépend de plusieurs bases de données et vous voulez vous assurer qu'elle peut s'exécuter correctement si elle bascule vers le serveur miroir. Étant donné que la mise en miroir fonctionne au niveau de la base de données, vous devez déterminer les bases de données qui sont critiques pour l'application et définir la mise en miroir pour chacune d'entre elles. Vous pouvez alors vous assurer que toutes les bases de données basculent ensemble et donc vous assurer que l'application fonctionne sur le serveur miroir.
Recherche d'entités dans une application qui exécutent des requêtes distribuées à l'aide de noms en quatre parties.
Vous voulez connaître les serveurs liés qui sont utilisés dans des requêtes distribuées.
Recherche des entités utilisées dans une application qui contient des références dépendant de l'appelant ou des références de nom en une partie.
Avant de déployer une application, vous pouvez déterminer si les entités utilisées par l'application contiennent des références dépendant de l'appelant ou des références aux entités qui utilisent uniquement un nom en une partie. De telles références indiquent de mauvaises méthodes de programmation et peuvent entraîner un comportement inattendu lorsque l'application est déployée. Cela est dû au fait que la résolution (liaison) de l'entité référencée dépend du schéma de l'appelant et que cette information n'est déterminée qu'au moment de l'exécution. Une fois ces références trouvées, les requêtes peuvent être corrigées en spécifiant le nom en plusieurs parties approprié tel que schema_name.object_name.
Pour plus d'informations sur les dépendances SQL, consultez Description des dépendances SQL.
Signalement des dépendances à l'aide des vues et des fonctions système
Pour afficher les dépendances SQL, SQL Server 2008 fournit l'affichage catalogue sys.sql_expression_dependencies et les fonctions de gestion dynamique sys.dm_sql_referenced_entities et sys.dm_sql_referencing_entities. Vous pouvez interroger ces objets pour retourner des informations de dépendance sur les entités définies par l'utilisateur.
Les dépendances SQL peuvent également être affichées à l'aide de l'option Afficher les dépendances dans SQL Server Management Studio. Pour plus d'informations, consultez Procédure : afficher les dépendances SQL (SQL Server Management Studio).
Utilisation de l'affichage catalogue sys.sql_expression_dependencies
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.
Utilisation de la fonction de gestion dynamique sys.dm_sql_referenced_entities
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.
Utilisation de la fonction de gestion dynamique sys.dm_sql_referencing_entities
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.
Exemples
Les exemples suivants retournent les dépendances SQL à l'aide de l'affichage catalogue sys.sql_expression_dependencies et des fonctions de gestion dynamique sys.dm_sql_referenced_entities et sys.dm_sql_referencing_entities.
Signalement des entités dont dépend une entité spécifiée
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 AdventureWorks;
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 AdventureWorks.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 AdventureWorks;
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 AdventureWorks 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 AdventureWorks;
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 AdventureWorks dbo MyTable NULL
NULL AdventureWorks dbo MyTable c1
NULL AdventureWorks 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 AdventureWorks;
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
Signalement des entités qui dépendent d'une entité spécifiée
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 AdventureWorks;
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 AdventureWorks;
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
Signalement des dépendances au niveau des colonnes
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 AdventureWorks;
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
Signalement des dépendances entre serveurs et de bases de données croisées
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 uniquement enregistré lorsque le nom est spécifié explicitement. 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
Signalement des références dépendant de l'appelant
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;
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;
Signalement des entités qui utilisent un UDT spécifié
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 AdventureWorks;
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
Signalement des dépendances de déclencheurs DDL au niveau du serveur
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;
Voir aussi