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

The Safe rename feature is a SQL refactor 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 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:

ApexSQL Refactor shows the following window for renaming a column:

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

If the code is checked, this is what will be seen:

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 it can be proceeded. with renaming the column. The High or Medium severity warnings will not be experienced because Safe rename feature will never allow to execute any operations that could break or damage a 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