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

After we explained the principles of the Safe rename feature in Part 1, in this sequel we will give you an insight in the execution steps.

If there is the possibility that renaming the object can put your database integrity at risk, the Safe rename feature will, immediately after clicking on the Preview button, display a red blinking exclamation mark sign next to the New name field, and it will not generate the preview script:

You can encounter only Low severity warnings, which are of informative nature:

  • A column with the name “ColumnName”” already exists
  • The column cannot be renamed. A column can be renamed for a table or a view
  • Cannot rename the column. Parent is null
  • Cannot rename columns for a system object
  • Only tables, views, procedures or functions can be renamed
  • The object ‘ObjectName’ cannot be renamed. The ‘SchemaName’ schema does not exist
  • The object ‘ObjectName’ cannot be renamed. The ‘UserName’ user does not exist
  • Cannot rename parameter ‘{Parameter}’ because the new name is a system variable name, or does not start from the ‘@’ sign, or contains a restricted symbol like ‘{Symbol}’
  • A parameter cannot be renamed. Parent not found
  • Cannot rename the return parameter
  • A function/procedure already contains a variable declaration with the specified parameter name
  • Cannot rename the column because the new name is empty
  • Cannot rename the object because the new name is empty
  • Columns of a filetable cannot be renamed

The Sequence tab lists sequences in order they are to be executed. ApexSQL Refactor will provide a concise list of actions ordered in such a manner that no dependencies would be broken when the actual renaming is initialized and the sequence actions are performed.

Dependent objects appear under the Dependencies tab, which lists all the objects that refer to the column to be renamed:

The Safe rename feature provides detailed preview of what is affected by the object renaming process and how the process will be executed. You can perform in detail analysis if needed, or you can simply rename the object by selecting the Rename button.

This feature is meant to be an improvement over the “sp_rename” stored procedure. To compare, let’s look at how this would be done

Rif you rename a column with the regular expression sp_rename:

EXEC sp_rename 'Person.Address.PostalCode', 'PCode','COLUMN'

The “Changing any part of an object name could break scripts and stored procedures” message appears.

This comes up as a warning that the renaming action, we have just executed, might have a negative effect on any stored procedures referencing the column. We have to check manually if any stored procedures are referencing the column prior to renaming it. Furthermore, if the column has a constraint, we must drop the constraint prior to executing the renaming command.

ApexSQL Refactor provides an effective solution to rename database objects in SQL Server, automates the renaming process, and prevents potential problems with broken dependencies.

Useful resources

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

February 27, 2015