Renaming a column in Sql Server

By | September 6, 2010
 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”.

5 thoughts on “Renaming a column in Sql Server

  1. admin Post author

    Nice article vivek, can you please put some more light on the relevance of the last parameter i.e. 'column'.

    Hope you got me, if we can use this statement to rename tables as well by changing the last parameter? Plz explain.

    Reply
  2. Vivek Johari

    HI Manish,
    Thanks for your comment. The last parameter 'column' is used since through the last parameter sql server identifies that we are going to rename the column of the table.

    For renaming the table, we don't need this parameter.The syntax for renaming the column is

    Exec sp_rename 'Oldtablename', 'Newtablename'

    Reply
  3. admin Post author

    hi vivek,

    thanks for the quick followup to this, but my doubt still remains there. If last parameter is only to get the input as column, then why not to leave it? and if, some other parameter can also be passed to have a different functionality, then what is it? Please clarify.

    Reply

Leave a Reply