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, a cost of refactoring both database and external applications that access the database vs. an impact of the achieved readability, should be considered.

Renaming columns and tables

To rename a table or a column the sp_rename procedure can be used. This method will not rename references to that table or a column automatically. Therefore, all dependencies of an object should be found and renamed 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

For example, if the Customer table needs to be renamed, first all of its dependencies needs to be listed. 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'

Now, the table (or a column) can be renamed with the following query:

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

As it can be seen 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 the body of the procedure need to be updated 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 the procedure usp_Customers needs to be altered:

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 the object should be dropped and recreated it with a new name.

Before renaming a view, all the information about objects and applications that reference a view need to be obtained. Using sp_helptext, it can be seen 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, the following query can be used:

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

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

Rename method in ApexSQL Refactor

From Object Explorer or Server Explorer choose a table table that needs to be renamed. Under the ApexSQL Refactor menu choose the Safe rename command:

In the Safe rename table window, enter the new name for a table and click the Preview button:

In the Sequence tab, it can be seen the list of all dependent objects and the order in which changes will be made:

To see the generated script, click the Create script button. It can be seen 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

 

Related posts:

  1. SQL Formatting standards – joins, lists, structure, operations
  2. Rules of SQL formatting – Terminating SQL statements with semicolons
  3. SQL Refactoring techniques – How to split a table in SQL
  4. Replacing one-to-many relationships with association tables automatically– Part 1
  5. Replacing one-to-many relationships with association tables automatically – Part 2
February 18, 2014