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'