Refactorisation de base de données SQL – Trouver et mettre à jour les références internes

Une des exigences les plus importantes dans le processus de refactorisation de bases de données SQL est que toute modification apportée à un schéma de base de données exige que tous les dépendances soient mises à jour et modifiées pour refléter les modifications apportées.

Toutes les méthodes SQL de refactorisation ne changent ni les valeurs des données stockées dans votre base de données ni les définitions d’objets. Il y a cependant quelques problèmes courants dont vous devez tenir compte indépendamment de la méthode de refactoring. Vous devez corriger toutes les contraintes cassées, les views, les triggers, les procédures stockées, les tables etc. Vous aurez besoin de trouver tous les objets et les données liés à un changement afin de pouvoir mettre à jour vos références internes.

Une dépendance est créée entre deux entités lorsqu’une entité est référencée par son nom à l’intérieur de la définition d’une autre entité. Une entité qui fait référence à une autre entité est appelée une entité de référence, et une entité qui est visée est appelé une entité référencée.

Image illustrating Referencing and Referenced entity

L’engine du SQL Server Database Engine suit deux types de dépendances:

Dépendances liées au schéma: C’est une relation entre deux entités qui empêche l’entité référencée d’être supprimée ou modifiée. Elle peut être créé à l’aide de la clause WITH SCHEMABINDING, lorsque vous créez une view ou une user-defined function, lorsqu’une table fait référence à une autre entité, comme une user-defined function ou un user-defined type dans la définition d’une colonne calculée ou une contrainte.

Dépendances liées au schema: C’est une relation entre deux entités dans laquelle l’entité référencée peut être supprimée.

Trouver les références internes avec SSMS

Vous pouvez trouver les dépendances de l’objet dans SQL Server Management Studio à l’aide de la fonctionnalité de View Dependencies. Sélectionnez un objet ou un type de données défini par l’utilisateur dans le volet Object Explorer, faites un clic droit, puis sélectionnez la commande View Dependencies:

Selecting the View Dependencies command in SSMS

Dans les champs Name, Type, et Dependency type vous pouvez alors voir un nom, un type ainsi qu’un type de dépendance.

Si vous choisissez l’option Objects that depend dans le dialogue Object Dependencies vous pouvez voir les objets référencés par la table Person :

Choosing the Objects that depend on option in the Object Dependencies dialog

Si vous choisissez l’option Objects on which depends dans le dialogue Object Dependencies vous pouvez voir les objets qui référencent la table Person :

Choosing the Objects on which depends option in the Object Dependencies dialog

Cette function n’est cependant pas valable pour les dépendances de colonnes.

Trouver les références internes en utilisant T-SQL

SQL Server® 2008 a introduit un nouvelle catalog view sys.sql_expression_dependencies, et deux dynamic management sys.dm_sql_referenced_entities et sys.dm_sql_referencing_entities pour garder une trace des dépendances.

La view système sys.sql_expression_dependencies contient une entrée pour chaque user defined objet qui a une dépendance avec un autre objet dans la base de données.

Voici un exemple de query pour trouver les dépendances d’un objet avec la view sys.sql_expression_dependencies :

SELECT 
referencing_object_name = o.name,
referencing_object_type_desc = o.type_desc, 
referenced_object_name = referenced_entity_name, 
referenced_object_type_desc =so1.type_desc
FROM sys.sql_expression_dependencies sed 
INNER JOIN sys.objects o 
ON sed.referencing_id = o.object_id
LEFT OUTER JOIN sys.objects so1 
ON sed.referenced_id =so1.object_id 
WHERE referenced_entity_name = 'Person'

Table containing the results gained by querying the sys.sql_expression_dependencies view

La fonction sys.dm_sql_referenced_entities retourne une ligne par user defined entité qui est référencée par nom dans la definition d’une autre entité.

Par exemple, si la user defined function dbo.ufnGetContactInformation est l’entité référencée, la fonction sys.dm_sql_referenced_entities retournera toutes les user defined entités qui sont référencés par nom dans la définition de la fonction dbo.ufnGetContactInformation (tables, fonctions, colonnes etc.)

SELECT DISTINCT
referenced_schema_name
, referenced_entity_name --Referenced entity name
, referenced_minor_name -- Referenced column name; NULL if a column is not referenced 
, referenced_minor_id
, referenced_class_desc
FROM sys.dm_sql_referenced_entities ('dbo.ufnGetContactInformation', 'OBJECT');
GO

Table containing the results gained by querying the sys.dm_sql_referenced_entities view

La fonction sys.dm_sql_referencing_entities retourne une entrée par user defined entité qui reference une autre user defined entité.

Par exemple, si la tabelle Person st l’entité spécifiée, la fonction sys.dm_sql_referencing_entities retournera toutes les user defined entitiés tels que les views, triggers, stored procedures etc., qui font référence à la table Person dans leur définition

SELECT 
referencing_schema_name
, referencing_entity_name
, referencing_class_desc
FROM sys.dm_sql_referencing_entities ('Person.Person', 'OBJECT');
GO

Table containing the results gained by querying the sys.dm_sql_referencing_entities view

Lorsque vous trouvez toutes les dépendances d’une entité que vous souhaitez modifier ou supprimer, vous aurez besoin de réécrire tous les objets référencés manuellement. Si vous renommez, par exemple la table Person en PersonInfo, vous aurez besoin d’appliquer la modification à toutes les entités référençant cette table.

Pour obtenir la definition d’un objet, vous pouvez utilizer la procedure sp_helptext trouvez l’object référencé dans la définition et appliquer le changement.

Dans l’example de la view vEmployeeDepartment qui reference la table Person nous allons changer le nom de la table Person pour PersonInfo:

Avant:

CREATE VIEW [HumanResources].[vEmployeeDepartment] 
AS 
SELECT 
    e.[BusinessEntityID] 
    ,p.[Title] 
    ,p.[FirstName] 
    ,p.[MiddleName] 
    ,p.[LastName] 
    ,p.[Suffix] 
    ,e.[JobTitle]
    ,d.[Name] AS [Department] 
    ,d.[GroupName] 
    ,edh.[StartDate] 
FROM [HumanResources].[Employee1] e
       INNER JOIN [Person].[Person] p
       ON p.[BusinessEntityID] = e.[BusinessEntityID]
    INNER JOIN [HumanResources].[EmployeeDepartmentHistory] edh 
    ON e.[BusinessEntityID] = edh.[BusinessEntityID] 
    INNER JOIN [HumanResources].[Department] d 
    ON edh.[DepartmentID] = d.[DepartmentID] 
WHERE GETDATE() BETWEEN edh.[StartDate] AND ISNULL(edh.[EndDate], GETDATE());

Après:

ALTER VIEW [HumanResources].[vEmployeeDepartment] 
AS 
SELECT 
    e.[BusinessEntityID] 
    ,p.[Title] 
    ,p.[FirstName] 
    ,p.[MiddleName] 
    ,p.[LastName] 
    ,p.[Suffix] 
    ,e.[JobTitle]
    ,d.[Name] AS [Department] 
    ,d.[GroupName] 
    ,edh.[StartDate] 
FROM [HumanResources].[Employee1] e
       INNER JOIN [Person].[PersonInfo] p
       ON p.[BusinessEntityID] = e.[BusinessEntityID]
    INNER JOIN [HumanResources].[EmployeeDepartmentHistory] edh 
    ON e.[BusinessEntityID] = edh.[BusinessEntityID] 
    INNER JOIN [HumanResources].[Department] d 
    ON edh.[DepartmentID] = d.[DepartmentID] 
WHERE GETDATE() BETWEEN edh.[StartDate] AND ISNULL(edh.[EndDate], GETDATE());

Trouver et mettre à jour les références internes à l’aide de ApexSQL Refactor

Avec ApexSQL Refactor’s il n’est pas nécessaire de vérifier les dépendances et de mettre à jour manuellement les références internes. Chaque fonctionnalité de refactorisation de ApexSQL Refactor possède un mécanisme pour trouver toutes les dépendances et mettre à jour les définitions des objets références automatiquement sans casser les dépendances.

Ci-dessous, une liste des entités qui référencent la table Person :

Dialog showing Referencing entities for the Person table

ApexSQL Refactor vous donne aussi la marche à suivre pour la refactorisation, par ordre d’occurrence:

ApexSQL Refactor gives you exact steps of the process of applying refactoring method

Lorsque vous ouvrez le script généré dans la fenêtre de requête SSMS vous pouvez voir que le script contient toutes les étapes nécessaires pour automatiquement appliquer toutes les modifications et actualiser toutes les dépendances.

Ci-dessous sont quelques exemples de changement faisant référence à des entités:

-- Alter View vEmployeeDepartment

ALTER VIEW [HumanResources].[vEmployeeDepartment] 
AS 
SELECT 
    e.[BusinessEntityID] 
    ,p.[Title] 
    ,p.[FirstName] 
    ,p.[MiddleName] 
    ,p.[LastName] 
    ,p.[Suffix] 
    ,e.[JobTitle]
    ,d.[Name] AS [Department] 
    ,d.[GroupName] 
    ,edh.[StartDate] 
FROM [HumanResources].[Employee1] e
       INNER JOIN [Person].[PersonInfo] p
       ON p.[BusinessEntityID] = e.[BusinessEntityID]
    INNER JOIN [HumanResources].[EmployeeDepartmentHistory] edh 
    ON e.[BusinessEntityID] = edh.[BusinessEntityID] 
    INNER JOIN [HumanResources].[Department] d 
    ON edh.[DepartmentID] = d.[DepartmentID] 
WHERE GETDATE() BETWEEN edh.[StartDate] AND ISNULL(edh.[EndDate], GETDATE());
GO

-- Alter Trigger iuPerson

ALTER TRIGGER [Person].[iuPerson] ON [Person].[PersonInfo] 
AFTER INSERT, UPDATE NOT FOR REPLICATION AS 
BEGIN
    DECLARE @Count int;

    SET @Count = @@ROWCOUNT;
    IF @Count = 0 
        RETURN;

    SET NOCOUNT ON;

    IF UPDATE([BusinessEntityID]) OR UPDATE([Demographics]) 
    BEGIN
        UPDATE [Person].[PersonInfo] 
        SET [Person].[PersonInfo].[Demographics] = N'<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"> 
            <TotalPurchaseYTD>0.00</TotalPurchaseYTD> 
            </IndividualSurvey>' 
        FROM inserted 
        WHERE [Person].[PersonInfo].[BusinessEntityID] = inserted.[BusinessEntityID] 
            AND inserted.[Demographics] IS NULL;
        
        UPDATE [Person].[PersonInfo] 
        SET [Demographics].modify(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
            insert <TotalPurchaseYTD>0.00</TotalPurchaseYTD> 
            as first 
            into (/IndividualSurvey)[1]') 
        FROM inserted 
        WHERE [Person].[PersonInfo].[BusinessEntityID] = inserted.[BusinessEntityID] 
            AND inserted.[Demographics] IS NOT NULL 
            AND inserted.[Demographics].exist(N'declare default element namespace 
                "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
                /IndividualSurvey/TotalPurchaseYTD') <> 1;
    END;
END;
GO

ApexSQL Refactor change automatiquement toutes les extended properties de l’objet:

-- Drop Extended Property MS_Description From iuPerson
EXEC sp_dropextendedproperty N'MS_Description', 'SCHEMA', N'Person', 'TABLE', N'Person', 'TRIGGER', N'iuPerson'
GO
-- Create Extended Property MS_Description On iuPerson
EXEC sp_addextendedproperty N'MS_Description', N'AFTER INSERT, UPDATE trigger inserting Individual only if the Customer does not exist in the Store table and setting the ModifiedDate column in the Person table to the current date.', 'SCHEMA', N'Person', 'TABLE', N'PersonInfo', 'TRIGGER', N'iuPerson'
GO

Ressources utiles:
Understanding SQL Dependencies
sys.sql_expression_dependencies (Transact-SQL)
sys.dm_sql_referenced_entities (Transact-SQL)
sys.dm_sql_referencing_entities (Transact-SQL)
Keeping sysdepends up to date in SQL Server 2008

Article traduit par Régis Baccaro

No related posts.

August 19, 2015