How to rename database schema and object names without breaking SQL database dependencies

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:

  1. Right-click a table to be renamed in the SQL Server Management Studio’s Object Explorer
  2. 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