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 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 ApexSQL Search from the menu and then the Safe rename option from the context menu 

This will open the Safe rename table dialog. The first thing that can be noticed is the red-blinking warning icon, which informs about the existence of a table with the same name under the specified schema. If we properly change the schema and table name, the warning will disappear.

After choosing another available schema and specifying the new table name, by clicking the Generate preview button, the following Preview tabs will be populated with information:

  • The 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 Smart rename 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 we can see, 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 you can simply and safely rename the object by selecting the Rename button

March 26, 2015