This article is about the impact of renaming a column of a table .



Introduction

As mentioned in MSDN BOL, renaming a column will not automatically rename references to that column. You must modify any objects that reference the renamed column manually.

Suppose there is a situation where you rename a column in the middle of your development. When we rename the column we will notice the below caution message in SSMS after successfully renaming that column : 
 
Caution: Changing any part of an object name could break scripts and stored procedures.

As clearly mentioned in the warning message, we should take care of column naming in the referenced Stored Procedures, Views , triggers and functions.

Let us walk through the effect of renaming a column with the below sample scripts:

 

--Create Sample table
  
 CREATE TABLE T_test (Col1 INT NOT NULL ,Col2 CHAR(2))
 GO
  
 --Create Sample Views accessing the above created test table
  
 CREATE VIEW V_test
 AS
 SELECT * FROM T_test
 GO
 CREATE VIEW V_test1
 AS
 SELECT Col1,Col2 FROM T_test
 GO
  
 --Create Sample Stored Procedures accessing the above created test table
  
 CREATE PROCEDURE P_test_get
 AS
 BEGIN
 SELECT * FROM T_test
 END
 GO
  
 CREATE PROCEDURE P_test_Insert
 AS
 BEGIN
 INSERT T_test (Col1,Col2)
 SELECT 1,'A'
 END
 GO
  
 --Create Sample Function accessing the above created test table
  
 CREATE FUNCTION F_test(@Col1 INT)
 RETURNS @ret TABLE
 (
     Col1 INT ,
     Col2 CHAR(2)
 )
 AS
 BEGIN
 INSERT @ret
 SELECT Col1,Col2 FROM T_test
 RETURN;
 END;
 GO
  
 --Create Sample Trigger accessing the above created test table
  
 CREATE TRIGGER Tr_test
 ON T_test
 AFTER DELETE
 AS
 RAISERROR ('Notify - No Auditing Implemeted', 16, 10);
 GO
  
 --Create Primary key & NonClustered index
  
 ALTER TABLE T_test
 ADD CONSTRAINT PK_ColName PRIMARY KEY CLUSTERED (Col1);
 GO
  
 CREATE NONCLUSTERED INDEX IX_T_test
     ON T_test (Col1,Col2);
 GO

 

Scripts to Identify all Referenced Objects affected by Column Rename

 

--Script to get the list of referenced objects with their definitions,
--before renaming the column
 DECLARE @ColumnName NVARCHAR(256) = 'Col1'
 ;With Depends
 AS
 (
 SELECT
  SCHEMA_NAME(AB.Schema_id)+'.'+SED.referenced_entity_name TableName,
  C.name ColumnName,
  SCHEMA_Name(AOB.schema_id)+'.'+AOB.name PackageName,
  AOB.type_desc PackageType ,
  SED.referencing_id PackageID ,
  SM.definition PackageDefinition
 FROM sys.columns C
  JOIN sys.all_objects AB
 ON C.Object_id = AB.Object_id
  JOIN sys.sql_expression_dependencies SED
 ON SED.referenced_id = AB.Object_id
  JOIN sys.all_objects AOB
 ON AOB.object_id = SED.referencing_id
  LEFT JOIN sys.sql_modules SM
 ON SM.object_id = SED.referencing_id
 WHERE C.name = @ColumnName
 )
 SELECT * INTO #temp FROM Depends
 UNION ALL
 SELECT SCHEMA_NAME(T.Schema_id)+'.'+Object_name(AOB.parent_object_id),
        C.name,
        SCHEMA_NAME(AOB.Schema_id)+'.'+tr.name,
        AOB.type_desc PackageType ,
        AOB.object_id,
        SM.definition
 FROM sys.triggers  Tr
  JOIN sys.all_objects AOB
 ON AOB.Object_id = Tr.Object_id
  JOIN sys.columns C
 ON C.object_id = AOB.parent_object_id
 JOIN sys.tables T
 ON T.Object_id = C.Object_id
  JOIN sys.sql_modules SM
 ON SM.object_id = Tr.Object_id
 WHERE C.name = @ColumnName
   
 SELECT * FROM #temp

Ways of Renaming a Column of a Table


Object Explorer - > Table - > Columns - > Right-Click - > Rename







 Right-Click on a Table - > Design - > Edit the column name




T-SQL Script to rename a column

EXEC SP_RENAME 'dbo.T_test.Col1', 'Col11', 'COLUMN';
GO

Error Number - 207

Note if we had used " * " instead of explicitly mentioning the columns in the above scripts, column renaming would not have any impact on the referenced objects.
 
What happens if we did not mention the column names explicitly inside batch scripts ?
 
What is error number - 207 ?

Scripts to Identify Referenced Objects that are affected by Column Rename
and need modification

 

--EXEC SP_RENAME 'dbo.T_test.Col1', 'Col11', 'COLUMN';
--GO
   
 --List of Views to be modified
 SELECT T.*,error_message FROM #temp T
 CROSS APPLY sys.dm_exec_describe_first_result_set(' SELECT * FROM '+PackageName,NULL ,0)
 WHERE PackageType = 'VIEW'
 AND error_number = 207
  
 --List of Stored Procedures to be modified
 SELECT T.*,error_message FROM #temp T
 CROSS APPLY sys.dm_exec_describe_first_result_set(PackageName,NULL ,0)
 WHERE PackageType = 'SQL_STORED_PROCEDURE'
 AND error_number = 207




Note that the Key columns in Primary key constraints and Indexes will be automatically renamed when a column is renamed.




Note that we cannot rename a column with a check constraint created using that column .

CREATE TABLE T_test1
 (
 City varchar(255)
 CONSTRAINT chk_Person CHECK (City='Chennai')
 )
 GO
  
EXEC SP_RENAME 'dbo.T_test1.City', 'Citys', 'COLUMN';
GO

Msg 15336, Level 16, State 1, Procedure sp_rename, Line 497
Object 'dbo.T_test1.City' cannot be renamed because the object participates in enforced dependencies.

 

 

Avoiding Renaming of Objects using DDL Triggers

 

We can prevent renaming objects using DDL triggers.

CREATE TRIGGER PreventObjectsRename
ON DATABASE
FOR RENAME
AS
PRINT 'disable trigger PreventColumnRename to rename objects'
ROLLBACK;
GO
  
EXEC SP_RENAME 'dbo.T_test.Col11', 'Col1', 'COLUMN';
GO


 
Note : We cannot rename a column even in the Object Explorer if we have a DDL trigger enabled for the RENAME event type .

 

 

--Drop all sample objects created for this demo
DROP TABLE T_test,T_test1
DROP VIEW V_test,V_test1
DROP PROCEDURE P_test_get,P_test_Insert
DROP FUNCTION F_test
DROP TRIGGER Tr_test
DROP TRIGGER PreventObjectsRename ON DATABASE

 


 

Related Reference links 


 

See Also