How to rename a column without breaking your SQL database (4290131)

Renaming a table or view column is not something a SQL developer needs to do every day. However, when the time comes, how can it be done easily and effortlessly?

Just renaming a column is not difficult. To rename the column TerritoryID in the Sales.SalesTerritory table into TerID, execute the following statement:

EXECUTE sp_rename 'Sales.SalesTerritory.TerritoryID', 'TerrID', 'COLUMN';

The problem is that you will get the following warning:

Caution: Changing any part of an object name could break scripts and stored procedures.

In addition, maybe even this:

Object ‘Sales.SalesTerritory.TerritoryID’ cannot be renamed because the object participates in enforced dependencies.

What are these scripts that will be broken?

For example, let’s say there is a Sales.vTer stored procedure that references the TerritoryID column in the Sales.SalesTerritory table. The sp_rename procedure will only rename the TerritoryID column in the Sales.SalesTerritory table, but not the one in the Sales.vTer stored procedure.

Therefore, the Sales.SalesTerritory table will contain the TerrID column, but the stored procedure will call Sales.SalesTerritory.TerritoryID, which does not exist anymore. The script will be broken, and its execution will fail.

What are enforced dependencies?

A dependency is “enforced” when the referencing object has a schema-bound expression. This message is also shown, if there are any other objects that need to be dropped and re-created with the new column name, in order to rename the column successfully.

How to find the stored procedures and other objects that will be affected by the column name change?

SQL Server Management Studio can track dependencies, via its View Dependencies option. It shows the objects that depend on the selected one, but the objects that reference the renamed column need to be updated one by one.

To rename a column using sp_rename, the following needs to be done:

1. Find all dependent objects that reference the table/view column
2. Drop all enforced dependencies
3. Rename the column by executing sp_rename
4. Recreate all enforced dependencies that were dropped in step #2
5. If there is a need to rename more than 1 column, repeat the steps 1 through 4

It is not an appealing process. It makes you want to give up the renaming, rather than continue with it.

How to rename a column without too much trouble?

ApexSQL Search can help. It’s a FREE SQL Server Management Studio and Visual Studio add-in which finds text in SQL database objects, and data in SQL database tables and views. It allows changing the tables, views, stored procedures, functions, columns, parameter names and schemas without breaking dependencies, while visualizing all object interdependencies.

1. Open SQL Server Management Studio or Visual Studio
2. In the Object Explorer/Server Explorer, navigate to a table or view column that want to rename
3. Right-click on the column and from the context menu, select the Safe rename command:



Click To See Full Image.

4. To see the changes that will be executed, click Preview button in the Safe rename column window
   
5. The Generated script tab shows the script that will be executed to rename the column:



Click To See Full Image.

6. Open the Warnings tab to see the information about the potential problems that are encountered during the renaming process:



Click To See Full Image.

7. Open the Sequence tab to see all the actions that will be executed during the renaming. These are the actions that need to be passed through manually if the sp_rename procedure is used:



Click To See Full Image.

8. Open the Dependencies tab to see the objects that depend on the renamed column. These are the objects that will be automatically modified to keep the scripts from breaking. If the sp_rename procedure is used, all of these objects need to be found and modified manually:



Click To See Full Image.

9. If there is a need to modify the script before it is executed, 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 by clicking Execute or save it to a file.

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