Monday, September 6, 2010

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".


5 comments:

  1. 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.

    ReplyDelete
  2. 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'

    ReplyDelete
  3. 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.

    ReplyDelete
  4. Hi vivek,
    can u please explain me how to write stored procedure for the inner join?

    ReplyDelete
    Replies
    1. Hi Swathi,
      Thanks for your comments.
      If you want to know , how to write a Stored Procedure, you can read my article
      http://vivekjohari.blogspot.in/2009/12/sql-server-how-to-write-stored.html

      And if you want to know about the joins (including inner joins) you can read my below article for SQL Joins

      http://vivekjohari.blogspot.in/2010/01/sql-joins-inner-joins-self-joins-outer.html

      Delete