SQL SERVER – How to Rename a Column Name or Table Name

I often get requests from blog reader for T-SQL script to rename database table column name or rename table itself.

Here is a video demonstrating the discussion

The script for renaming any column :
sp_RENAME 'TableName.[OldColumnName]' , '[NewColumnName]', 'COLUMN'

The script for renaming any object (table, sp etc) :
sp_RENAME '[OldTableName]' , '[NewTableName]'

This article demonstrates two examples of renaming database object.

  1. Renaming database table column to new name.
  2. Renaming database table to new name.

In both the cases we will first see existing table. Rename the object. Test object again with new name.

1. Renaming database table column to new name.

Example uses AdventureWorks database. A small table with name “Table_First” is created. Table has two fields ID and Name.

SQL SERVER - How to Rename a Column Name or Table Name spRename1

Now, to change the Column Name from “Name” to “NameChange” we can use command:

USE AdventureWorks
GO
sp_RENAME 'Table_First.Name', 'NameChange' , 'COLUMN'
GO

Following Fig. show use of SP_RENAME Command

SQL SERVER - How to Rename a Column Name or Table Name spRename2

You can see the column name “Name” is now changed to “NameChange“.

USE AdventureWorks
GO
SELECT *
FROM Table_First
GO

Following fig. verify that the column name has been changed.

SQL SERVER - How to Rename a Column Name or Table Name spRename3

2.Renaming database table to new name.

We can change the table name too with the same command.

sp_RENAME 'Table_First', 'Table_Last'
GO

Following fig. Shows how we can change Table Name.

SQL SERVER - How to Rename a Column Name or Table Name spRename4

Now, the table name “Table_First” is renamed as “Table_Last”.

“Table_First” will no longer be available in database. We can verify this by running script:

USE AdventureWorks
GO
SELECT *
FROM Table_First
GO

The Messages shows an error “Invalid object name ‘Table_First’.”

To check that the new renamed table exist in database run script:

USE AdventureWorks
GO
SELECT *
FROM Table_Last
GO

SQL SERVER - How to Rename a Column Name or Table Name spRename6

You can see the same data now available in new table named “Table_Last”

Reference : Pinal Dave (https://blog.sqlauthority.com)

,
Previous Post
SQLAuthority News – Ahmedabad SQL Server User Group Meeting – August 2008
Next Post
SQLAuthority News – SQL Server Express 2008 Downloads

Related Posts

225 Comments. Leave new

  • Thank you, to you i found what i needed.

    Reply
  • Thanks a lot.

    Reply
  • thankssss

    Reply
  • thanx…

    Reply
  • Kunal Choudhary
    November 3, 2015 12:32 pm

    i want to ask a question ?

    can we extract the characters from a query based on there position ?

    for Example : There is a string ‘ABCDEFGHIJKLMNOPQ’

    I want to Extract character at even postion.

    Reply
  • while rename a column i have given [ ](bracket while rename a column) so it resulted with the bracket under that column like ‘[price]’. how to rename it as just ‘price’

    sp_rename ‘pricing.[price ]’ , ‘[price]’

    sp_rename ‘pricing.[price]’ , ‘price’
    this above query is not working.

    Reply
  • sp_rename ‘pricing.[price ]’ , ‘[price]’

    due to above query the column is showing with [ ] eg..'[price]’. How to change it into ‘price’
    means without bracket

    sp_rename ‘pricing.[price ]’ , ‘price’
    this query is not working, kindly advice me

    thanks

    Reply
    • Mritunjay Sharma
      August 5, 2017 12:14 am

      Use Double quotes to rename it without braces:

      EXEC SP_RENAME ‘pricing.”[price ]”‘, ‘price’,’COLUMN’

      Reply
  • sp_RENAME ‘TableName.[OldColumnName]’ , ‘[NewColumnName]’, ‘COLUMN’

    Square brackets should not be used around NewColumnName. If used Column name will have [] which causes a lot of confusion while joining the table.

    Reply
  • Thanks. It was very helpful to me

    Reply
  • If rename a columna name.

    Caution: Changing any part of an object name could break scripts and stored procedures.

    How search column name in stored procedures, triggers, etc ?

    Reply
  • hai sir,
    i Renamed the table name with following command

    sp_RENAME ‘[OldTableName]’ , ‘[NewTableName]’
    i got messege

    Caution: Changing any part of an object name could break scripts and stored procedures.

    But table name is changed

    after that
    i Executed table with newtable name
    am getting invaled object in u r Database

    but table is showing in database design

    please give reply as soon as possible

    Reply
  • Make sure to have removed brackets [ ] before exec new name script. If you are using a saved query to execute the table that has had the name altered make sure to reflect this change within the query script.

    Reply
  • Hi, I would like to know how can I have certification for Advanced SQL. And where can I get reading materials to prepare for Adv. SQL

    Reply
  • thanks!

    Reply
  • Hi, quite useful article, but I think you should change example, from “sp_RENAME” to “sp_rename”. The procedure is lowercase.
    Normally you will not encounter any problems, but when I was running this against DB with CS collation I was getting error that procedure not exists. It took me “few” minutes, before I realised that it was just because I use “sp_RENAME” instead of “sp_rename” :)

    Reply
  • what happens if I run the same sp_rename over a column multiple times? Is the command idempotent?

    Reply
  • Hi Pinal,

    Does this sp_rename will be logged into default trace ?

    Reply
  • Hi Pinal,
    Is it possible to change name of column in the VIEW instead of TABLE with this query ?if not, whats your suggestion to change column name of VIEW for 1000 VIEWS?

    sp_RENAME ‘VIEW Name.[OldColumnName]’ , ‘[NewColumnName]’, ‘COLUMN’

    Reply
    • Instead of renaming column, you can alter the view and use different alias name for the column that you want to rename

      Reply
  • Biswaranjan Singh
    August 26, 2019 9:47 pm

    How to rename multiple columns in a table using sp_rename ?

    Reply
  • How to rename multiple columns in a table using sp_rename ?

    Reply

Leave a Reply

Menu