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, you have to modify all objects that depend on the one that you changed.

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 you want to rename
  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 you want 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 you want to rename
  3. Right-click on the object and select the Safe rename option from the ApexSQL Search menu

  4. In the Safe rename dialog’s New name text box, type a new name. ApexSQL Search checks that the name is not already in use. If the name is not valid, a red warning sign will blink next to the New name text box:

  5. Click the Generate preview button:

    The Preview section displays summary information:

    • The Generated script tab shows a complete SQL script of the renaming process
    • The Warnings tab displays 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 you want to modify the script before you execute it, click the Open 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 by clicking the Execute option, or save it to a file
  7. To execute the script as is, click the Rename button

Renaming an object can be easy. You don’t have to analyze whole database and search for dependencies yourself. Use free SQL database tool ApexSQL Search to do that for you. It will find all dependent objects, rename and drop–recreate when necessary, to avoid breaking the scripts.

April 4, 2013