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

All SQL refactoring methods change either the values of the data stored within your database or an object definitions there a couple of common issues you need to consider when applying any refactoring method. You need to fix all broken constraints, views, triggers, stored procedures, tables etc. You’ll need to find all the objects and data connected to a change to be able to update your 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

Image illustrating Referencing and 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

You can find object’s dependencies in SQL Server Management Studio 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:

Selecting the View Dependencies command in SSMS

In the Name, the Type, and the Dependency type text boxes you can see a name, type, and a type of the dependency respectively

By choosing the Objects that depend on option in the Object Dependencies dialog you can see the entities referenced by the Person table:

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

By choosing the Objects on which depends option in the Object Dependencies dialog you can see the referencing entities for the Person table:

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

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

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

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

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

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

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

When you find all the dependencies of an entity you want to modify or drop, you’ll need to rewrite all referenced objects manually. If you rename, for example the Person table to a PersonInfo, you need to apply the change to all listed referencing entities

To get the definition of an object you can use the sp_helptext procedure, find the referenced object in the object’s definition, and apply the change

In example of the vEmployeeDepartment view that references the Person table we will change the name of the Person table 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 you don’t have the need to check object’s dependencies and update internal references 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:

Dialog showing Referencing entities for the Person table

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

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

When you open the generated script in the SSMS query window you can see 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

April 2, 2014