SQL database refactoring techniques – Rename method


Why rename?

Rename Table, Rename Column, and Rename View are structural refactoring. The main purpose to apply Rename Column, Rename Table, and Rename View are increasing readability, adopting database naming conventions, or clarifying the meaning of an object. In the case of renaming objects you should consider a cost of refactoring both database and external applications that access the database vs. an impact of the achieved readability

Renaming columns and tables

To rename a table or a column you can use the sp_rename procedure. This method will not rename references to that table or a column automatically. Therefore you should find all dependencies of an object and rename them manually. To find dependencies of a table use sys.sql_expression_dependencies before renaming it

To find objects that depend on the Customer table use the following query:

USE AdventureWorks2012;
GO
SELECT 
referenced_schema_name,
referenced_entity_name,
referencing_class_desc

FROM sys.sql_expression_dependencies
WHERE referencing_id = OBJECT_ID('Sales.vIndividualCustomer'); 
GO

Dialog showing dependencies of the SQL Server table

If you want to rename the table “Customer”, for example, first you need to list all its dependencies. The following query lists all objects on which the Customer table depends:

USE AdventureWorks2012
GO
SELECT
referencing_schema_name = SCHEMA_NAME(so.SCHEMA_ID),
referencing_object_name = so.name,
referencing_object_type_desc = so.type_desc,
referenced_schema_name,
referenced_object_name = referenced_entity_name,
referenced_object_type_desc = so1.type_desc

FROM sys.sql_expression_dependencies sed
INNER JOIN sys.objects so 
ON sed.referencing_id = so.[object_id]
LEFT OUTER JOIN sys.objects so1 
ON sed.referenced_id = so1.[object_id]
WHERE referenced_entity_name = 'Customer'

Dialog showing lists all objects on which the SQL Server table depends

Now you can rename the table (or a column) with the following query:

EXEC sp_rename 'Sales.Customer', 'Customers';

As you can see from the previous result set, there are objects on which the Customer table depends and they need to be changed manually to reflect the changes. For example, the Customers table depends on a stored procedure usp_Customers, so you need to update the body of the procedure with a new table name, as well as all the other dependent objects

CREATE PROCEDURE usp_Customers
AS
SELECT * FROM AdventureWorks2012.Sales.Customer
GO

To reflect the changes you need to alter procedure usp_Customers :

ALTER PROCEDURE usp_Customers
AS
SELECT * FROM AdventureWorks2012.Sales.Customers
GO

After renaming a table, update all listed dependent objects to reflect the changes

Renaming views

sp_rename procedure is not recommended as a method for renaming stored procedures, views, user defined functions, and triggers but instead you should drop the object and recreate it with a new name

Before renaming a view, you need to obtain all the information about objects and applications that reference a view. Using sp_helptext, you can see the body of a view

CREATE VIEW [Sales].[vIndividualCustomer] 
AS 
SELECT 
    p.[BusinessEntityID]
     ,p.[Title]
     ,p.[FirstName]
     ,p.[MiddleName]
     ,p.[LastName]
     ,p.[Suffix]
     ,pp.[PhoneNumber]
        ,pnt.[Name] AS [PhoneNumberType]
     ,ea.[EmailAddress]
     ,p.[EmailPromotion]
     ,at.[Name] AS [AddressType]
     ,a.[AddressLine1]
     ,a.[AddressLine2]
     ,a.[City]
     ,[StateProvinceName] = sp.[Name]
     ,a.[PostalCode]
     ,[CountryRegionName] = cr.[Name]
     ,p.[Demographics]
FROM [Person].[Person] p
     INNER JOIN [Person].[BusinessEntityAddress] bea 
     ON bea.[BusinessEntityID] = p.[BusinessEntityID] 
     INNER JOIN [Person].[Address] a 
     ON a.[AddressID] = bea.[AddressID]
     INNER JOIN [Person].[StateProvince] sp 
     ON sp.[StateProvinceID] = a.[StateProvinceID]
     INNER JOIN [Person].[CountryRegion] cr 
     ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
     INNER JOIN [Person].[AddressType] at 
     ON at.[AddressTypeID] = bea.[AddressTypeID]
         INNER JOIN [Sales].[Customer] c
         ON c.[PersonID] = p.[BusinessEntityID]
         LEFT OUTER JOIN [Person].[EmailAddress] ea
         ON ea.[BusinessEntityID] = p.[BusinessEntityID]
         LEFT OUTER JOIN [Person].[PersonPhone] pp
         ON pp.[BusinessEntityID] = p.[BusinessEntityID]
         LEFT OUTER JOIN [Person].[PhoneNumberType] pnt
         ON pnt.[PhoneNumberTypeID] = pp.[PhoneNumberTypeID]
WHERE c.StoreID IS NULL;

GO

Use CREATE VIEW and DROP VIEW to rename a view

To list dependencies that need to be updated with a new name of a view you can use the following query:

USE AdventureWorks2012;
GO
SELECT 
referenced_schema_name,
referenced_entity_name,
referencing_class_desc

FROM sys.sql_expression_dependencies
WHERE referencing_id = OBJECT_ID('Sales.vIndividualCustomer'); 
GO

Dialog showing a list of dependencies that need to be updated with a new name of a view

After renaming a view ensure that all objects, applications, and scripts are updated to reflect the changes

Rename method in ApexSQL Refactor

Under the ApexSQL Refactor menu choose the Safe rename feature. In the Safe rename table dialog enter the new name for a table and click the Preview button

Entering new table name in ApexSQL Refactor's Smart Rename Table dialog

In the Sequence tab, you can see the list of all dependent objects and the order in which changes will be made

The Sequence tab of ApexSQL Refactor's Smart Rename feature

To see the generated script, click the Create script button. You can see that all dependent objects are altered. For example, here is code for altering the procedure uspUpdateEmployeeLogin, generated by ApexSQL Refactor:

-- Alter Procedure uspUpdateEmployeeLogin

ALTER PROCEDURE [HumanResources].[uspUpdateEmployeeLogin]
    @BusinessEntityID [int], 
    @OrganizationNode [hierarchyid],
    @LoginID [nvarchar](256),
    @JobTitle [nvarchar](50),
    @HireDate [datetime],
    @CurrentFlag [dbo].[Flag]
WITH EXECUTE AS CALLER
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        UPDATE [HumanResources].[Employees]
        SET [OrganizationNode] = @OrganizationNode 
            ,[LoginID] = @LoginID 
            ,[JobTitle] = @JobTitle 
            ,[HireDate] = @HireDate 
            ,[CurrentFlag] = @CurrentFlag 
        WHERE [BusinessEntityID] = @BusinessEntityID;
    END TRY
    BEGIN CATCH
        EXECUTE [dbo].[uspLogError];
    END CATCH;
END;
GO

Useful resources:

Books Online for SQL Server – Rename Tables (Database Engine)
Books Online for SQL Server – Rename Columns (Database Engine)
Books Online for SQL Server – Rename Views

February 18, 2014