![]() The Generated script tab shows the script that will be executed to rename the column: To see the changes that will be executed, click Preview button in the Safe rename column window Right-click on the column and from the context menu, select the Safe rename command: In the Object Explorer/Server Explorer, navigate to a table or view column that want to rename Open SQL Server Management Studio or Visual Studio It allows changing the tables, views, stored procedures, functions, columns, parameter names and schemas without breaking dependencies, while visualizing all object interdependencies. It’s a SQL Server Management Studio and Visual Studio add-in which finds text in SQL database objects, and data in SQL database tables and views. How to rename a column without too much trouble?ĪpexSQL Search can help. ![]() It makes you want to give up the renaming, rather than continue with it. 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. 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. |