![]() If there is a need to rename more than 1 column, repeat the steps 1 through 4 Recreate all enforced dependencies that were dropped in step #2 To rename a column using sp_rename, the following needs to be done:įind all dependent objects that reference the table/view column 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. SQL Server Management Studio can track dependencies, via its View Dependencies option. #SQL SERVER RENAME COLUMN HOW TO#How to find the stored procedures and other objects that will be affected by the column name change? 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. The script will be broken, and its execution will fail.Ī dependency is “enforced” when the referencing object has a schema-bound expression. Therefore, the Sales.SalesTerritory table will contain the TerrID column, but the stored procedure will call, which does not exist anymore. 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. ![]() What are these scripts that will be broken?įor example, let’s say there is a Sales.vTer stored procedure that references the TerritoryID column in the Sales.SalesTerritory table. Object ‘’ cannot be renamed because the object participates in enforced dependencies. The problem is that you will get the following warning:Ĭaution: Changing any part of an object name could break scripts and stored procedures. ![]() EXECUTE sp_rename '', 'TerrID', 'COLUMN' ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |