How to rename a column without breaking your SQL database

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:


The problem is that you will get the following warning:


In addition, maybe even this:


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.


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:

  2. Drop all enforced dependencies
  3. Rename the column by executing sp_rename
  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?


  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:
  4. To see the changes that will be executed, click Preview button in the Safe rename column window
  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:
  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:
  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


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.


April 4, 2013