SQL SERVER – How to Rename a Column Name or Table Name « Journey to SQL Authority with Pinal Dave

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

August 26, 2008 by pinaldave

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

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.

Renaming database table column to new name.
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.

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

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.

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.

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 GOSELECT *
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

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

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Posted in Pinal Dave, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, T SQL, Technology | 22 Comments

22 Responses

on August 27, 2008 at 7:12 am SDC

That is winning info!
on August 28, 2008 at 3:33 pm Bhanu

Hi Pinal,

If we change the cloumn name, suppose if the column has a index on it, will the change of name effect the index already created.
on August 29, 2008 at 1:27 pm satay

hi,
This is use full info
If the column have a constraints how can rename the column
on September 1, 2008 at 12:19 pm Ved Jha

Thanks for the info.
Will u guide me how to alter existing constraint
on September 3, 2008 at 12:06 am Dharmesh Kalaria

Hi,
Yes if we have not null constraint in column we can able to change the column name.
on September 18, 2008 at 4:06 pm Arshad

Hi,
Thanks it works.
on September 26, 2008 at 2:04 pm Srivas

Hi Guys,

This is very Useful Information.Thank you.
on September 26, 2008 at 2:09 pm Srivas

without using Sp_Rename Stored procedure how can i
rename the table?

Is any other Query to do this?
on September 28, 2008 at 7:01 am SQL SERVER - Renaming SP is Not Good Idea - Renaming Stored Procedure Does Not Update sys.procedures Journey to SQL Authority with Pinal Dave

[...] 2008 by pinaldave I have written many articles about renaming a tables, columns and procedures SQL SERVER – How to Rename a Column Name or Table Name, here I found something interesting about renaming the stored procedures and felt like sharing it [...]
on October 7, 2008 at 12:59 pm Robert J Collin

Thank you for sharing. This is a great glob post. Great info and rendering (live the screens etc).
on October 16, 2008 at 2:32 pm Donepudi

thnq very much sir………
on December 27, 2008 at 4:36 pm karthe

Thank you sir for giving a valuable information
on December 27, 2008 at 4:40 pm karthe

Thank you very much sir for giving a valuable information
on January 11, 2009 at 9:15 pm ZUBAIR ALAM

I read your articles it is fabulous!!!!
pls try to understand following coding in sql server 2005

SELECT C.NAME,C.ADDRESS,SUM(O.QUANTITY*P.PRICE)
FROM MYCUSTOMER C JOIN ORDERS O
ON C.NAME=O.NAME JOIN PART P
ON O.PARTNUM=P.PARTNUM

Its giving error:–column ‘MYCUSTOMER.NAME’ is invalid in the select list because it is not contained in either an aggregate function or the group by clause

I tried many thing but nothing works
plz look into it…..
thanks in advance!!
on January 12, 2009 at 4:39 pm Sree

Alam,

Just add GROUP BY C.NAME,C.ADDRESS at the end and try.
All the best…
on January 12, 2009 at 8:49 pm lloyd

thanx a lot it works fine
on January 13, 2009 at 10:38 pm ZUBAIR ALAM

thanks SREE…….
GROUP BY clause has to be used since c.name and c.address columns have one to many relationship with other columns.
on February 2, 2009 at 9:05 pm John D

This is not supported going forward. Avoid using SP_RENAME where possible or you will have to edit your code when you upgrade to SQL 2008..
on February 11, 2009 at 5:14 pm ritesh

hii….bhai tera bahut bahut dhanyawad….
on March 7, 2009 at 1:06 pm Ramesh

hi,

i want to rename the table column using sql queries. can anyone help me ?

Thanks,
Ramesh
on April 16, 2009 at 5:22 pm Sunder

hi

thanx sir this is very nice
on April 23, 2009 at 4:43 pm Shital Kasliwal

Hi,
This Script is Running Correctly.

Thanks Sir.