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

About vivekjohari

Database Consultant with more than 11.5 years of experience in database designing & programming and DBA related activities.  Had good experience on different databases like SQL Server, MySQL & Oracle, Azure SQL &  Big Data.
This entry was posted in SQL Basic Concepts, SQL Server and tagged , . Bookmark the permalink.

13 Responses to Renaming a column in Sql Server

  1. admin says:

    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.

  2. Vivek Johari says:

    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'

  3. admin says:

    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.

  4. swathi says:

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

  5. Pingback: armaniqq

  6. Pingback: طراحی سایت

  7. Pingback: light pink backdrop

  8. Pingback: sahabatqq

  9. Pingback: dadupoker

  10. Pingback: sahabatqq

  11. Pingback: Remi

  12. Pingback: rajaqq

Leave a Reply