SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Renaming a column in Sql Server

 We often need to change the name of a column of a table to a new name. We  can do this with the help of the Exec Sp_rename command.

 The Syntax of the Sp_rename is given below:-

 Exec sp_rename 'TableName.[OldColumnName]', '[NewColumnName]', 'Column'

 For example, suppose we have a table called Employee who has the following structure:-

 CREATE TABLE Employee (Emplyeeid int identity(1,1),
                                      Empnumber nvarchar(10),
                                      Firstname nvarchar(150), 
                                      Lastname nvarchar(150),
                                      Age int, 
                                      Phoneno nvarchar(15), 
                                      Address nvarchar(200),
                                      Empdate datetime)

Now suppose we insert the following data into the table Employee

Insert into Employee values('VIV123', 'Vivek', 'Johari', 27, '9211134', 'Delhi', getdate())

If we execute the Select command into the table Employee, we get the following result:-

Suppose now  we want to change the column name Firstname to Fname the we use the given query:-

Exec Sp_rename 'Employee.Firstname', 'Fname', 'column'

If we again execute the Select command into the table Employee, we get the following result:-

Here the column name "Firstname" changed to the column "Fnamn".

Vivek's SQL Notes

Vivek johari is currently a Analyst and have more that 5.5 yeras of experience in database. He has Master degree in Computer and also he is Microsoft certified Sql DBA (MCTS)& Microsoft certified SQl BI professional(MCTS). He is also Oracle certified profession(OCP)DBA in ORACLE 10g and ORACLE 9i.He has the experience of working in PL/SQL, T-SQL and SSIS/SSRS. His work basically involved designing and optimization of the Database.He has also published many database articles on his blog Technologies with Vivek Johari.


No comments.

Leave a Comment

Please register or log in to leave a comment.