How to rename database objects in a SQL Server database safely – Part 1

The Safe rename feature is a SQL code formatter feature in ApexSQL Refactor. This feature makes possible to rename objects in SQL Server without breaking the database dependencies. It generates a SQL script that changes the object name and updates all the dependent database objects.

The Safe rename feature can be applied to the following objects: database objects (tables, views, procedures and functions), table/view columns and function/procedure parameters. It is located under the main menu of our free SQL database tool ApexSQL Refactor:

ApexSQL Refactor can be used via the context menu. Right click on the object and select the Safe rename command from the ApexSQL Refactor menu:

ApexSQL Refactor shows the following dialog for renaming a column:

In this following example, the column PostalCode is renamed to PCode. Once we change the name and click the Preview button, we can immediately preview the generated script in the Generated script tab.

If we look at the code, this is what we will see:

The column PostalCode is renamed to PCode in the table definition in the index (DROP/CREATE INDEX combination) and in the VIEW:

DROP INDEX [IX_Address_City_PostalCode] ON [Person].[Address]
GO

EXEC sp_rename N'[Person].[Address].[PostalCode]'
 ,N’PCode’
 ,'COLUMN'
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_Address_City_PostalCode] 
  ON [Person].[Address] (
 [City]
 ,PCode
 )
GO

ALTER VIEW [HumanResources].[vEmployee]
AS
SELECT e.[EmployeeID]
  ,a.[City]
  ,sp.[Name] AS [StateProvinceName]
  ,a.[PCode]
FROM [HumanResources].[Employee] e
  INNER JOIN [HumanResources].[EmployeeAddress] ea 
  ON e.[EmployeeID] = ea.[EmployeeID]
  INNER JOIN [Person].[Address] a 
  ON ea.[AddressID] = a.[AddressID]
  INNER JOIN [Person].[StateProvince] sp 
  ON sp.[StateProvinceID] = a.[StateProvinceID]

If there are any problems detected during the renaming process, the Warning tab will show a message regarding the problems that may occur, as well as a recommendation on what action to take in order to avoid potential issues. In this particular case, there were no errors detected and we can proceed with renaming the column. You will not experience any High or Medium severity warnings because Safe rename feature will never allow you to execute any operation that could break or damage your database

More about the execution steps in Part 2 of the article.

Useful resources

How to rename database objects
Rename using sp_rename stored procedure
Considerations before renaming database objects

February 27, 2015