How to change an object name without breaking your SQL database

Very often, initial database design does not fit the current criteria and requirements anymore. It is therefore necessary to periodically align to what our current needs and standards are. When working with a large database, any idea of changing the name of one or more SQL objects can be a real challenge.

Changing an object name is a simple operation and it could be done easily using the sp_rename system stored procedure or by utilizing the rename and alter … rename statements for indexes and triggers.

The problem is the fact that this change is not propagated to other objects that depend on the renamed object – they become invalid and return an error when used. To avoid this, all objects that depend on the object that is changed need to be modified.

How to find the objects that depend on the renamed object

SQL Server Management Studio can track dependencies and show the objects that depend on the selected one through its View Dependencies option.

1. Use SQL Server Management Studio, View dependencies for the object that needs to be renamed
2. Update the DDL script for the dependent object with the new name of the renamed object
3. Repeat steps #1 and #2 for all dependent objects, individually
4. If there is a need to rename more than 1 object, repeat the steps 1 through 3

For a large number of objects, this can take quite a while and like every manual process, it is prone to mistakes.

How to rename an object without too much trouble

ApexSQL Search is a SQL Server Management Studio and Visual Studio add-in capable of smart renaming objects like tables, views, procedures and functions, as well as table/view columns and function/procedure parameters without breaking database dependencies.

To smart rename an object:

1. Open SQL Server Management Studio or Visual Studio
2. In the Object explorer/Server explorer, find the object that needs to be renamed
3. Right-click on the object and select the Safe rename command from the ApexSQL Search menu
   

4. In the Safe rename window under the New name text box, type a new name. ApexSQL Search checks that entered name is not already in use. If the name is not valid, a warning icon next to the New name text box will appear:
   

5. Click the Preview button:

   

   The Preview section displays summary information:

   • The Generated script tab shows a complete SQL script of the renaming process
   • The Warnings tab shows any potential problems and reasons why the script might fail
   • The Sequence tab shows the sequence of the actions that the script will perform
   • The Dependencies tab displays a list of the objects that need to be altered to maintain all dependencies
   

6. If there is need to modify the script before execution, click the Create script button. The generated script will be opened in the SQL Server Management Studio Query editor/Visual Studio editor.

   Review, modify if needed and execute it with a click on the Execute option, or save it to a file.

Renaming an object can be easy. There is no need to analyze a whole database and search for dependencies yourself. Use SQL database tool ApexSQL Search to do that. It will find all dependent objects, rename and drop–recreate when necessary, and avoid breaking the scripts.