Renaming a table or view column is not something a SQL developer needs to do every day. But 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 TerrID, execute the following statement:
EXECUTE sp_rename 'Sales.SalesTerritory.TerritoryID', 'TerrID', 'COLUMN';
The problem is that you’ll get the following warning:
Caution: Changing any part of an object name could break scripts and stored procedures.
Or 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
So, 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 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`s 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 objects’ interdependencies
- Open SQL Server Management Studio or Visual Studio
- In the Object explorer/Server explorer, find the table or view column you want to rename
- Right-click on the column and select ApexSQL Search, Smart rename
- To see the changes that will be executed, click Generate preview in the Smart 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 scriptThe 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 now
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
Author Milena Petrovic