SQL, How to rename a column name

In this blog I will show you how to rename a column name in sql. The syntax for renaming the column is

EXEC SP_RENAME
@objname = 'table_name.old_column_name',
@newname = 'new_column_name',
@objtype = 'COLUMN'

According to the msdn:

"sp_rename" Changes the name of a user-created object in the current database. This object can be a table, index, column, alias data type, or Microsoft .NET Framework common language runtime (CLR) user-defined type.

Below is the sample script which will explain you how to rename a column name .

GO

IF EXISTS ( SELECT  *
            FROM    SYS.OBJECTS
            WHERE   OBJECT_ID = OBJECT_ID(N'[dbo].[TEST_COLUMNRENAME_TABLE]')
                    AND OBJECTPROPERTY(OBJECT_ID, N'IsUserTable') = 1 ) 
    DROP TABLE [DBO].[TEST_COLUMNRENAME_TABLE]

GO

CREATE TABLE TEST_COLUMNRENAME_TABLE
    (
      ID INT,
      [FIRSTNAME] VARCHAR(20)
    )

GO

INSERT  INTO [TEST_COLUMNRENAME_TABLE]
        SELECT  1,
                'TEST1'
        UNION ALL
        SELECT  2,
                'TEST3'
        UNION ALL
        SELECT  3,
                'TEST3'
        UNION ALL
        SELECT  4,
                'TEST4'
        UNION ALL
        SELECT  5,
                'TEST5'

GO

--NOW WE WILL CHANGE THE NAME OF COLUMN [FIRSTNAME] TO [LASTNAME]

EXEC SP_RENAME @objname = 'TEST_COLUMNRENAME_TABLE.FIRSTNAME',
    @newname = 'LASTNAME', @objtype = 'COLUMN'

GO

SELECT  *
FROM    [TEST_COLUMNRENAME_TABLE]

GO

--OUTPUT

--ID LASTNAME

------------- --------------------

--1 TEST1

--2 TEST3

--3 TEST3

--4 TEST4

--5 TEST5

So from the output as you have seen the column name has been renamed.

Do let me know your feedback,comments.