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?
Say you have 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 you have to drop and re-create 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 you still have to update the objects that reference the renamed column one by one.
To rename a column using sp_rename, you need to:
- Find all dependent objects that reference the table/view column
- Drop all enforced dependencies
- Rename the column by executing sp_rename
- Recreate all enforced dependencies that were dropped in step #2
- If you want 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.
- Open SQL Server Management Studio or Visual Studio
- In the Object Explorer/Server Explorer, navigate to a table or view column you want to rename
- Right-click on the column and select the Safe rename option from the ApexSQL Search menu:
- To see the changes that will be executed, click Generate preview in the Safe rename column dialog
- The Generated script tab shows the script that will be executed to rename the column
- Open the Warnings tab to see the information about the potential problems that are encountered during the renaming process
- Open the Sequence tab to see all the actions that will be executed during the renaming. These are the actions you would have to go through manually if you used the sp_rename procedure
- 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 you used the sp_rename procedure, you would have to manually find and modify them
If you want to modify the script before you execute it, click 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 Execute or save it to a file
- To execute the script as is, click Rename button
Renaming a column can be easy and FREE. You don’t have to analyze your whole database and search for dependencies yourself. Use 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