How to Rename a Column Name or Table Name in SQL Server - SQL Server / T-SQL Tutorial Part 36

Scenario:

You are working as SQL Server developer for Auto Insurance Company. On one of  SQL Server Instance, they have Database with name Insurance. In Insurance Database they have table dbo.Customer with below definition.

Create Table Customer
(FirstName VARCHAR(50),
LastName VARCHAR(50),
Age SmallInt,
PhoneNumber CHAR(9),
DOB Date,
Sex CHAR(1)
)



You got the requirement to change the name of Customer table to USCustomers and also change the Sex column Name to Gender. You need to prepare SQL Scripts for QA, UAT and Production.


Solution:

Rename Table in SQL Server:

You can use system Stored Procedure to rename Table in SQL Server.

sp_rename 'OldTableName' , 'NewTableName'

Let's rename our Customer table to USCustomer by using sp_rename Stored Procedure.

EXEC sp_rename 'Customer','USCustomer'


Rename Column in SQL Server Table:
To Rename column in SQL Server Table, we can use the sp_rename system Stored Procedure as well. The only thing we have to add it to is 'Column' Parameter at the end.

sp_rename 'TableName.OldColumnName' , 'NewColumnName' ,  'Column'

Let's use sp_rename to change Column Name from Sex to Gender in dbo.Customer Table.

EXEC sp_rename 'Customer.Sex','Gender', 'Column'


Video Demo : How to rename SQL Server Table or Column in Table in SQL Server



No comments:

Post a Comment