Many times we come across a scenario where we need to rename / change the existing table column name. We can use the SP_RENAME system stored to change/rename the table column name. In this article we will cover the following:

1. Rename table column name using SP_Rename system stored procedure
2. Why SP_RENAME returns the warning: “Caution: Changing any part of an object name could break scripts and stored procedures.” when renaming the column name?
3. How to rename table column which is enclosed by “[” and “]” (i.e. Square brackets) symbol
4. How to rename table column which has . (i.e. dot) symbol in the column name
5. How to rename temporary table column name
6. How to rename temporary table variable column name

To understand table column rename using SP_RENAME with extensive list of examples, let us first create a Customer table with sample records as depicted in the below image by using the following script:

Script:

USE TEMPDB
GO
--Create Customer Table for the Demo
CREATE TABLE dbo.Customer(Id INT, CustName NVARCHAR(50), 
[[PhoneNo]]] VARCHAR(20), [Zip Code] VARCHAR(10), 
[City.Name] VARCHAR(50))
GO
--Add sample records to the Customer table
INSERT INTO dbo.Customer(id, CustName, [[PhoneNo]]],
	[Zip Code], [City.Name])
VALUES(1,'Basavaraj Biradar', '123-123', '560001', 'Bangalore'),
      (2,'Shreeganesh Biradar', '321-321', '560025', 'Mysore')
GO 
--Create Procedure to get all the records from customer table
CREATE PROCEDURE dbo.GetCustomers
AS 
	SELECT id, CustName, [[PhoneNo]]],
		[Zip Code], [City.Name]
	FROM dbo.Customer
GO
--Get customer table data
EXEC dbo.GetCustomers
GO

1. Rename table column name using SP_Rename system stored procedure

Below is the SYNTAX of the SP_RENAME system stored procedure for renaming the column name:

SYNTAX:

SP_RENAME 'TableName.OldColumnName' , 'NewColumnName', 'COLUMN'

Example 1: Rename Customer table column CustName to FullName using SP_RENAME

SP_RENAME 'Customer.CustName' , 'FullName', 'COLUMN'

Result:

Below is the pictorial representation of the table column rename using SP_RENAME

2) Why SP_RENAME returns the warning: “Caution: Changing any part of an object name could break scripts and stored procedures.” when renaming the column name?

Basically, sql server returns this warning to alert us to update all the places wherever we are referring this column. For example take a scenario where if the old column name is still referred in a stored procedure even after the column rename using sp_rename. In this scenario, the sp will start throwing error post the column rename. To avoid such issues, Sql server alerts us by returning the warning when we use SP_RENAME.

Example:The script at the beginning of this article is creating the stored procedure: GetCustomers and it was working. Now let us verify whether it is still working after renaming the column CustName to FullName by the previous example:

--Get customer table data
EXEC dbo.GetCustomers
GO

RESULT:

Msg 207, Level 16, State 1, Procedure GetCustomers, Line 4
Invalid column name ‘CustName’.

From the above result it is clear that column rename using SP_RENAME will just change the column name in the table. It will to change in all the places, it is developer’s responsibility to update at all the place. Let us modify the GetCustomers stored procedure to reflect the new column name using the below script and then verify the stored procedure execution:

–Modify the stored procedure to refer the new column

ALTER PROCEDURE dbo.GetCustomers
AS 
	SELECT id, FullName, [[PhoneNo]]],
		[Zip Code], [City.Name]
	FROM dbo.Customer
GO
--Now the storde procedure works
EXEC dbo.GetCustomers
GO

RESULT:

3. How to rename table column which is enclosed by “[” and “]” (i.e. Square brackets) symbol

Example: The Customer table created at the beginning of this article has a column [PhoneNo]. As Square bracket has special meaning it doesn’t work straight forward. Let us try renaming by the below script:

SP_RENAME 'Customer.[PhoneNo]' , 'PhoneNo', 'COLUMN'
GO

RESULT:

Msg 15248, Level 11, State 1, Procedure sp_rename, Line 238
Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong.

The reason for the above error is, sql server treats Customer.[PhoneNo] as Customer.PhoneNo. To solve this issue we have to add two open square brackets at the beginning of PhoneNo column and three closing square brackets at the end of the PhoneNo column as shown below:

SP_RENAME 'Customer.[[PhoneNo]]]' , 'PhoneNo', 'COLUMN'
GO
SELECT * FROM dbo.Customer

RESULT:

4. How to rename table column which has . (i.e. dot) symbol in the column name

Example: The Customer table created at the beginning of this article has a column City.Name. This column has . (i.e. dot symbol) in it’s name, let us try renaming by the below script:

SP_RENAME 'Customer.City.Name' , 'City', 'COLUMN'
GO

RESULT:

Msg 15248, Level 11, State 1, Procedure sp_rename, Line 238
Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong.

The reason for the above error is, sql server treats Customer.City.Name as three part naming convention and treating City as table name and Name as column name and Customer as Schema. Because of this it is not finding the column name in the table definition and failing to update it. To solve this issue we have to enclose column name within square brackets as shown in the below script:

SP_RENAME 'Customer.[City.Name]' , 'City', 'COLUMN'
GO
SELECT * FROM dbo.Customer

RESULT:

5. How to rename temporary table column name

Renaming a temporary table column name is same as that of the regular table column name.

Example: Let us create a temporary table #Customer and insert a record by using the below script

CREATE TABLE #Customer(Id INT)
GO
INSERT INTO #Customer (Id) Values ('100')
GO

Now we can rename the temporary table #Customer column Id name to SlNo using the below script:

SP_RENAME '#Customer.ID' , 'SlNo', 'COLUMN'
GO

6. How to rename temporary table variable column name

Sql Server doesn’t support the renaming of the Table Variable column name

Example: Let us verify this behaviour with an example

DECLARE  @Customer TABLE(id INT)
EXEC SP_RENAME '@i.ID' , 'SlNo', 'COLUMN'

RESULT:

Msg 15248, Level 11, State 1, Procedure sp_rename, Line 238
Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong.