Refactorización de bases de datos SQL – Encontrando y actualizando referencias internas

Uno de los requerimientos más importantes en el proceso de refactorización de una base de datos SQL es que cualquier cambio hecho en un esquema de base de datos requiere que todas las dependencias sean actualizadas y cambiadas para reflejar los cambios que usted hizo.

Todos los métodos de refactorización de SQL cambian o los valores de los datos almacenado en su base de datos o las definiciones de un objeto. Hay un par de problemas comunes que usted necesita considerar cuando aplique cualquier método de refactorización. Usted necesita reparar todas las restricciones, vistas, desencadenadores, procedimientos almacenados, tablas, etc. Usted necesitará encontrar todos los objetos y datos conectados a un cambio para poder actualizar sus referencias internas.

Una dependencia es creada entre dos entidades cuando una entidad aparece por nombre dentro de una definición de otra entidad. Una entidad que referencia a otra entidad es llamada entidad referente, y una entidad que es referida es llamada entidad referenciada.

SQL Server Database Engine rastrea dos tipos de dependencias:

Dependencias ligadas por esquema es una relación entre dos entidades que evita que la entidad referenciada sea borrada o alterada. Puede ser creada usando la cláusula WITH SCHEMABINDING cuando se está creando una vista o una función definida por el usuario, cuando una tabla referencia otra entidad, como una función definida por el usuario o un tipo definido por el usuario en una restricción o en la definición de una columna computada.

Dependencias no ligadas por esquema es una relación entre dos entidades donde la entidad referenciada puede ser borrada o modificada.

Encontrando referencias internas en SSMS

Usted puede encontrar dependencias de objetos en SQL Server Management Studio usando la característica View Dependencies. Seleccione un objeto, o un tipo de dato definido por el usuario en el panel Object Explorer, haga clic derecho y seleccione el comando View Dependencies:

En los cuadros de texto Name, Type y Dependency type usted puede ver un nombre, un tipo y un tipo de dependencia respectivamente.

Al elegir la opción Objects that depend on en el diálogo Object Dependencies usted puede ver las entidades referenciadas por la tabla Person

Al elegir la opción Objects on which depends en el diálogo Object Dependencies usted puede ver las entidades referentes para la tabla Person:

De todas maneras, esta característica no está disponible para dependencias de columna de tabla SQL.

Encontrando referencias internas usando T-SQL

SQL Server® 2008 introdujo una nueva vista de catálogo sys.sql_expression_dependencies, y dos funciones de administración dinámica sys.dm_sql_referenced_entities y sys.dm_sql_referencing_entities para hacer un seguimiento de las dependencias de objeto.

La vista de sistema sys.sql_expression_dependencies mantiene un registro por cada objeto definido por el usuario, el cual tienen dependencia en otro objeto dentro de la base de datos actual.

Ejemplo de encontrar dependencias internas de objeto en una tabla consultando la vista 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'

La función sys.dm_sql_referenced_entities retorna un registro por entidad definida por el usuario que es referenciada por nombre dentro de la definición de una entidad referenciada especificada.

Por ejemplo, si la función definida por el usuario dbo.ufnGetContactInformation es la entidad referente especificada, la función sys.dm_sql_referenced_entities retorna todas las entidades definidas por el usuario que son referenciadas por nombre la definición de función dbo.ufnGetContactInformation como tablas, funciones, columnas, 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

La función sys.dm_sql_referencing_entities retorna un registro por entidad definida por el usuario, dentro de la base de datos actual, que hace referencia a otra entidad definida por el usuario por nombre.

Por ejemplo, si la tabla Person es la entidad referenciada especificada, la función sys.dm_sql_referencing_entities retorna todas las entidades definidas por el usuario, como vistas, desencadenadores, procedimientos almacenados, etc., que hacen referencia a la tabla Person por nombre en su definición.

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

Cuando usted encuentre todas las dependencias de una entidad que quiere modificar o borrar, usted necesitará reescribir todos los objetos referenciados manualmente. Si usted renombre, por ejemplo, la tabla Person a PersonInfo, usted necesitará aplicar el cambio a todas las entidades referentes listadas.

Para obtener la definición de un objeto usted puede usar el procedimiento sp_helptext encuentre el objeto referenciado en la definición de objeto y aplique el cambio.

Como ejemplo de la vista vEmployeeDepartment que referencia a la tabla Person cambiaremos el nombre de la tabla Person a PersonInfo:

Antes:

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

Despué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());

Encontrando y actualizando referencias internas usando ApexSQL Refactor

Usando las propiedades de refactorización de ApexSQL Refactor usted no tiene la necesidad de verificar las dependencias de objeto y actualizar las referencias internas manualmente. Cada característica de refactorización en ApexSQL Refactor tiene un mecanismo para encontrar todas las dependencias y actualizar definiciones en los objetos referentes automáticamente sin romper las dependencias.

Aquí están listadas las entidades referentes de la tabla Person:

ApexSQL Refactor también le dará los pasos exactos del proceso de aplicar un método de refactorización, el cual le mostrará las acciones en el orden en que ocurren:

Cuando usted abre el script generado en la ventana de consultas SMSS, usted puede ver que un script en la ventana de consultas contiene todos los pasos necesarios para aplicar automáticamente todos los cambios y actualizar todas las dependencias.

Aquí están algunos ejemplos de las entidades referentes alteradas:

-- 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 también aplicará automáticamente cambios en las propiedades extendidas de un objeto:

-- 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

Fuentes útiles:
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

Traductor: Daniel Calbimonte