• Article

Description 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épendante d'une autre entité. Une entité qui référence une autre entité dans sa définition et dont la définition est stockée dans le catalogue système est appelée entité de référence. Une entité qui est référencée par une autre entité est appelée une entité référencée. Il existe deux types de dépendance suivis par Moteur de base de données.

  • Dépendance liée au schéma

    Une dépendance liée au schéma est une relation entre deux entités qui empêche l'entité référencée d'être supprimée ou modifiée tant que l'entité de référence existe. Une dépendance liée au schéma est créée lorsqu'une vue ou une fonction définie par l'utilisateur est créée à l'aide de la clause WITH SCHEMABINDING. Une dépendance liée au schéma peut également être créée lorsqu'une table référence une autre entité, telle qu'une fonction définie par l'utilisateur, un type défini par l'utilisateur ou une collection de schémas XML Transact-SQL, dans une contrainte CHECK ou DEFAULT ou dans la définition d'une colonne calculée. La spécification d'un objet à l'aide d'un nom en deux parties (schema_name.object_name) ne constitue pas une référence liée au schéma.

  • Dépendance non liée au schéma

    Une dépendance non liée au schéma est une relation entre deux entités qui n'empêche pas l'entité référencée d'être supprimée ou modifiée.

L'illustration ci-dessous montre un exemple de dépendance SQL.

Description d'une dépendance SQL

L'illustration présente deux entités : la procédure X et la procédure Y. La procédure X contient une expression SQL qui possède une référence de nom à la procédure Y. La procédure X est appelée « entité de référence » alors que la procédure Y est appelée « entité référencée ». Comme la procédure X dépend de la procédure Y, la procédure X échouera suite à une erreur d'exécution si la procédure Y n'existe pas. En revanche, la procédure Y n'échouera pas si la procédure X n'existe pas.

L'exemple ci-dessous montre comment la procédure stockée X peut dépendre de la procédure stockée Y.

USE tempdb;
GO
CREATE PROCEDURE dbo.Y AS
SELECT * FROM sys.objects
GO
CREATE PROCEDURE dbo.X as
    EXEC dbo.Y;
GO

Pour voir la dépendance de X sur Y, exécutez la requête ci-dessous.

SELECT * 
FROM sys.sql_expression_dependencies 
WHERE referencing_id = OBJECT_ID('X')
    AND referenced_id = OBJECT_ID('Y')
    AND referenced_schema_name = 'dbo'
    AND referenced_entity_name = 'Y'
    AND referenced_database_name IS NULL
    AND referenced_server_name IS NULL;
GO

Types d'entités de référence et d'entités référencées

Le tableau suivant répertorie les types des entités pour lesquelles les informations de dépendance sont créées et gérées. Le tableau indique si l'entité est suivie comme une entité de référence ou une entité référencée. Les informations de dépendance ne sont ni 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.

Type d'entité

Entité de référence

Entité référencée

Table

Oui*

Oui

Vue

Oui

Oui

Procédure stockée Transact-SQL**

Oui

Oui

Procédure stockée CLR

Non

Oui

Fonction Transact-SQL définie par l'utilisateur

Oui

Oui

Fonction CLR définie par l'utilisateur

Non

Oui

Déclencheur CLR (DML et DDL)

Non

Non

Déclencheur DML Transact-SQL

Oui

Non

Déclencheur DDL au niveau de la base de données Transact-SQL

Oui

Non

Déclencheur DDL au niveau du serveur Transact-SQL

Oui

Non

Procédures stockées étendues

Non

Oui

File d'attente

Non

Oui

Synonyme

Non

Oui

Type (alias et type CLR défini par l'utilisateur)

Non

Oui

Collection de schémas XML

Non

Oui

Fonction de partition

Non

Oui

* Une table est suivie comme une entité de référence uniquement lorsqu'elle 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, contrainte CHECK ou 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.

Comment les informations de dépendance sont suivies

Le Moteur de base de données assure automatiquement le suivi des informations de dépendance lorsque des entités de référence sont créées, modifiées ou supprimées ; par ailleurs, il enregistre ces informations dans le catalogue système de SQL Server. Par exemple, si vous créez un déclencheur qui référence une table, une dépendance entre ces entités est enregistrée. Si vous supprimez par la suite le déclencheur, les informations de dépendance sont supprimées du catalogue système.

Contrairement aux versions antérieures de SQL Server, dans lesquelles les dépendances étaient suivies par ID, les dépendances sont maintenant suivies par nom. Cela signifie que le Moteur de base de données suit les informations de dépendance entre deux entités même si l'entité référencée n'existe pas au moment de la création de l'entité de référence. Cela peut se produire en raison de la résolution différée des noms. Par exemple, une procédure stockée qui référence une table peut être créée avec succès mais ne pas être exécutée, même si la table référencée n'existe pas dans la base de données. Le Moteur de base de données enregistre la dépendance entre la procédure et la table. Toutefois, un ID pour la table ne peut pas être enregistré car l'objet n'existe pas encore. Si la table est créée ultérieurement, l'ID de la table est retourné avec les autres informations de dépendance.

Les informations de dépendances sont suivies lorsque l'entité référencée apparaît par nom dans une expression SQL permanente de l'entité de référence. Les informations de dépendance sont obtenues lorsque les entités sont référencées par nom de la manière suivante :

  • À l'aide de l'une des instructions suivantes dans la définition d'un moduleTransact-SQL:

    • Instructions DML (Data Manipulation Language) : SELECT, INSERT, UPDATE, DELETE, MERGE

    • EXECUTE

    • DECLARE

    • SET (Lorsque SET est utilisé avec une fonction définie par l'utilisateur ou un type défini par l'utilisateur. Par exemple, DECLARE @var int; SET @var = dbo.udf1.)

    Les entités référencées dans la définition d'un module Transact-SQL à l'aide des instructions DDL (Data Definition Language) telles que CREATE, ALTER ou DROP, ne sont pas suivies.

  • À l'aide des instructions CREATE, ALTER ou DROP TABLE lorsque les instructions ne sont pas dans un module Transact-SQL et que l'entité référencée est une fonction définie par l'utilisateur, un type défini par l'utilisateur ou une collection de schémas XML Transact-SQL défini dans une colonne calculée, une contrainte CHECK ou une contrainte DEFAULT.

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 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 les noms en plusieurs parties valides, consultez Conventions de syntaxe de Transact-SQL (Transact-SQL).

Les limitations suivantes s'appliquent :

  • Les dépendances entre serveurs pour les instructions OPENROWSET, OPENQUERY et OPENDATASOURCE ne sont pas suivies.

  • Les dépendances pour l'instruction EXEC ('…') AT linked_server ne sont pas suivies.

Le tableau suivant récapitule les dépendances entre serveurs et de bases de données croisées qui sont suivies et les informations qui sont enregistrées dans le catalogue système et signalées par sys.sql_expression_dependencies (Transact-SQL).

Expression SQL dans un module

Est suivie

Nom du serveur référencé

Nom de la base de données référencée

Nom du schéma référencé

Nom de l'entité référencée

SELECT * FROM s1.db2.sales.t1

Oui

s1

db2

sales

t1

SELECT * FROM db3..t1

Oui

 

db3

 

t1

EXEC db2.dbo.Proc1

Oui

 

db2

dbo

proc1

EXEC ('…') AT linked_srv1

Non

 

 

 

 

EXEC linked_svr1.db2.sales.proc2

Oui

linked_svr1

db2

sales

proc2

Effet du classement sur le suivi des dépendances

Un classement détermine les règles de tri et de comparaison des données. Le classement de la base de données est utilisé pour identifier les informations de dépendance des entités dans la base de données. Par exemple, si une procédure stockée référence les entités Some_Table et SOME_TABLE dans une base de données qui utilise un classement sensible à la casse, les informations de dépendance pour deux entités sont enregistrées parce qu'une comparaison des deux noms indique qu'ils ne sont pas les mêmes. Toutefois, si la base de données utilise un classement non sensible à la casse, seule une dépendance unique est enregistrée.

Pour les dépendances entre serveurs et de bases de données croisées, le classement du serveur sur lequel l'objet de référence réside est utilisé pour résoudre le nom du serveur et de la base de données. Le classement de la base de données active est utilisé pour résoudre le nom du schéma et les noms d'objets.

Considérons la définition de procédure stockée suivante. Si la procédure stockée est créée dans une base de données avec un classement sensible à la casse sur une instance de SQL Server avec un classement du serveur non sensible à la casse, deux dépendances sont enregistrées pour les entités srv_referenced.db_referenced.dbo.p_referenced et srv_referenced.db_referenced.DBO.P_REFERENCED.

CREATE PROCEDURE p_referencing AS
    EXECUTE srv_referenced.db_referenced.dbo.p_referenced
    EXECUTE srv_referenced.db_referenced.DBO.P_REFERENCED
    EXECUTE SRV_REFERENCED.DB_REFERENCED.dbo.p_referenced;

Résolution de références ambiguës

Une référence est ambiguë lorsqu'elle peut être convertie au moment de l'exécution en une fonction définie par l'utilisateur, un type défini par l'utilisateur ou une référence xquery à une colonne de type xml.

Considérons la définition de procédure stockée suivante.

CREATE PROCEDURE dbo.p1 AS 
    SELECT column_a, Sales.GetOrder() FROM Sales.MySales;

Au moment de la création de la procédure stockée, il n'est pas possible de savoir si Sales.GetOrder() est une référence à une fonction définie par l'utilisateur nommée GetOrder du schéma Sales ou à une colonne nommée Sales de type défini par l'utilisateur ayant une méthode nommée GetOrder(). Lorsqu'une référence est ambiguë, la dépendance est signalée comme étant ambiguë en affectant la valeur 1 à la colonne is_ambiguous dans sys.sql_expression_dependencies et sys.dm_sql_referenced_entities. Les informations de dépendance suivantes sont signalées :

  • La dépendance entre la procédure stockée et la table.

  • La dépendance entre la procédure stockée et la fonction définie par l'utilisateur. Si la fonction existe, l'ID de la fonction est signalé ; sinon, l'ID est NULL.

  • La dépendance sur la fonction est marquée comme ambiguë. Autrement dit, is_ambiguous a la valeur 1.

  • Les dépendances au niveau des colonnes ne sont pas signalées car l'instruction dans laquelle les colonnes sont référencées ne peut pas être liée.

Gestion des dépendances

Moteur de base de données gère à la fois les dépendances liées au schéma et les dépendances non liées au schéma. Ces dépendances sont automatiquement actualisées lors des opérations qui affectent le suivi des dépendances, comme la mise à niveau d'une base de données d'une version antérieure de SQL Server ou la modification du classement d'une base de données.

Historique des modifications

Mise à jour du contenu

Suppression de l'instruction « Les dépendances de colonnes ne sont pas suivies pour les fonctions table CLR » dans la section Remarques.