The main purpose of renaming an object or a column in SQL Server is to increase readability, adopt naming conventions, or to give a more meaningful name to an object. The sp_rename procedure will not rename nor update all SQL database dependencies for a renamed object or a column automatically. Therefore, all object dependencies should be found by using e.g. the sys.sql_expression_dependencies function and renamed manually.
ApexSQL Search, a SQL Server Management Studio and Visual Studio add-in among other features, provides users the Safe rename feature – the ability to rename tables, procedures, views, functions and columns without breaking SQL database dependencies
Let us show the complete process of changing the name and schema of a table at the same time.
To use the Safe rename feature:
-
Right-click a table to be renamed in the SQL Server Management Studio’s Object Explorer
- Select the Safe rename command from the context menu:
This will open the Safe rename table window. The first thing that can be noticed is the yellow warning icon, which informs about the existence of a table with the same name under the specified schema. If the schema and table name are properly changed, the warning will disappear:
After choosing another available schema and specifying the new table name, with a click on the Preview button, the following Preview tabs will be populated with information:
- Generated script
- Warnings
- Sequence
- Dependencies
The Generated script tab
The preview tab displays a SQL script that will be executed in order to rename the selected table. It can be opened in a new query window by selecting the Open button, ready for editing or executing via SQL Server Management Studio:
The Warnings tab
Second in order, the Warnings tab, will provide warnings, for example, in case the rename process cannot be completed without a table rebuild. Such scenarios are very rare and complicated to demonstrate, but the Safe rename feature is aware of them and will raise a warning if this is the case.
The Sequence tab
This tab summarizes the generated SQL script in a tree-like form by showing actions in the order they will be performed. Please note that the action sequence is specifically generated in such a way as to prevent any dependency problems:
The Dependencies tab
Finally, the Dependencies tab, provides a list of dependent objects that refer to the table which will be renamed. These are handled via Alter statements in the generated SQL script by changing their references accordingly:
As it can be seen, the Safe rename feature provides a detailed preview in several categories of what’s affected and how an object rename process will be executed. These can be further analyzed, or simply and safely rename the object with a click on the Execute (F5) button.
March 26, 2015
