SQL database refactoring – Finding and updating internal references

One of the most important requirements in the process of SQL database refactoring is that any change made in a database schema requires all dependences to be updated and changed to reflect the changes which are made.

All SQL refactoring methods change either the values of the data stored within a database or an object definition and there a couple of common issues which need to consider when applying any refactoring method. All broken constraints, views, triggers, stored procedures, tables etc., need to be fixed. All objects and data connected to a change need to be found, to be able to update internal references.

A dependency is created between two entities when one entity appears by name inside a definition of another entity. An entity that references another entity is called a referencing entity, and an entity that is referred to is called a referenced entity:

The SQL Server Database Engine tracks two types of dependencies:

• Schema –bound dependencies is a relationship between two entities that’s preventing the referenced entity from being dropped or altered. It can be created by using the WITH SCHEMABINDING clause when creating a view or user-defined function, when a table references another entity, such as a user-defined function or user-defined type in a constraint or in the definition of a computed column

• Non- schema bound dependencies is a relationship between two entities where the referenced entity can be dropped or modified

Finding internal references in SSMS

The object’s dependencies in SQL Server Management Studio can be found using the View Dependencies feature. Select an object, or a user-defined data type in the Object Explorer pane, right-click, and select the View Dependencies command:

In the Name, the Type, and the Dependency type text boxes a name, type, and a type of the dependency can be seen respectively.

By choosing the Objects that depend on option, in the Object Dependencies window the entities referenced by the Person table can be seen:

By choosing the Objects on which depends option in the Object Dependencies window the referencing entities for the Person table can be seen:

This feature is not available for SQL table column dependencies though

Finding internal references using T-SQL

SQL Server® 2008 introduced a new catalog view sys.sql_expression_dependencies, and two dynamic management functions sys.dm_sql_referenced_entities and sys.dm_sql_referencing_entities to keep track of object dependencies.

The sys.sql_expression_dependencies system view holds one record each for a user defined object which has dependency on another object within the current database.

Example of finding internal object dependencies on a table by querying the sys.sql_expression_dependencies view:

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'

The sys.dm_sql_referenced_entities function returns one record per user defined entity that is referenced by name within the definition of a specified referencing entity.

For example, if the user defined function dbo.ufnGetContactInformation is the specified referencing entity, the sys.dm_sql_referenced_entities function returns all the user defined entities that are referred by name in the dbo.ufnGetContactInformation function definition such as tables, functions, columns 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

The sys.dm_sql_referencing_entities function returns one record per user defined entity, within the current database, that refers to another user defined entity by name.

For example, if the table Person is the specified referenced entity, the sys.dm_sql_referencing_entities function returns all the user defined entities, such as views, triggers, stored procedures etc., that refer the Person table by name in their definition:

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

When all dependencies of an entity that need to be modified or droped are found, all referenced objects need to be rewritten manually. For example, if the Person table is renamed into a PersonInfo, the change needs to be applied to all listed referencing entities.

To get the definition of an object the sp_helptext procedure can be used to find the referenced object in the object’s definition, and apply the change.

In example of the vEmployeeDepartment view that references the Person table the name of the Person table is changed into PersonInfo:

Before:

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

After:

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

Finding and updating internal references using ApexSQL Refactor

Using ApexSQL Refactor’s refactoring features the object’s dependencies and update internal references don’t need to be checked manually. Every refactoring feature in ApexSQL Refactor has a mechanism to find all dependencies and update definitions of the referencing objects automatically without breaking dependencies

Listed here are referencing entities for the Person table:

ApexSQL Refactor will also give the exact steps of the process of applying refactoring method, which will show all actions in order in which they occur:

When the generated script is opened in the SSMS query window, it can be seen that a script in the query window contains all the steps needed to automatically apply all changes and update all dependencies

Here are some examples of the altered referencing entities:

-- 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 will also automatically apply changes in the extended properties of an object:

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

Useful resources:
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