SQL Server - Effects Of Renaming a Column Of a Table

2024-01-17

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

Share via