After the principles of the Safe rename feature were explained in the Part 1, in this sequel an insight in the execution steps will be provided.
If there is the possibility that renaming the object can put a database integrity at risk, the Safe rename feature will immediately, after a click on the Preview button, display a warning icon next to the New name field, and it will not generate the preview script:
Only Low severity warnings can be encountered, 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 filletable 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 users detailed preview of what is affected by the object renaming process and how the process will be executed. The analysis in detail can be performed if needed, or can be simply renamed with a click on 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
If a column is renamed 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, that was just executed, might have a negative effect on any stored procedures referencing the column. It needed to be checked manually if any stored procedures are referencing the column prior to renaming it. Furthermore, if the column has a constraint, the constraint prior to executing the renaming command must be dropped.
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
