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
[youtube=http://www.youtube.com/watch?v=5xviNDISwis]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
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
You can see the same data now available in new table named “Table_Last”
Reference : Pinal Dave (https://blog.sqlauthority.com)
227 Comments. Leave new
Hi,
This Script is Running Correctly.
Thanks Sir.
Hi pinal,
thnx for the info.
Hi Pinal,
I think there is no need to pass third parameter as ‘column’ for renaming the column.
sp_rename ‘Table_First.Name’, NameChange
i have just tried without mentioning ‘column’ and it works fine.
May i know what is the importance of the third parameter ‘column’? Is it required or not?
If you omit, by default it is column. Otherwsie you need to specify where it is column, index, database, etc
This is really useful info
Thanks for providing very good and to the point info
Thanx a lot these is really helpfull, You really can make articles very simple to understand….
Once again Pinal Dave’s blog to the rescue! Thanks again for a great blog.
I have used the renaming mistakenly and my column name now looks like this “[column]” without the quotation mark but including the [] charecters. How can I reissue the command to rename the column? what escape sequence should I use.
i want 2 change a column name that contains a point(.). e.g . using the sp_rename it is giving an error. plz. solve d problem guys
Plz write this query ur problem will be solved.
sp_rename ‘[table name].[oldcolumn name]’, ‘newcolumnname’
I have used the renaming mistakenly and my column name now looks like this “[column]” without the quotation mark but including the [] charecters. How can I reissue the command to rename the column? what escape sequence should I use.
eg:: if coloumn name is txtname
after renaming using sp_rename with new coloumn name it is showing [txtname_new].
Now I cant even retrieve the values for that coloumn.
Please help me guys on this…
Thanks in advance.
@Uttam
Use the visual editor. Right click on the TABLE and choose “design”. You can change the name there.
Thanks Brian Sir.
It really helped.
Really appreciate your quick help.
THanks once again.
thanks sir,
it was urgent to me find the ans. for my exam
Thanks for this information
This is a great find, but it’s not exactly what I need.
We’re looking at upgrading our servers from 2005 to 2008. This will also involve a name change for our servers. Is there an easy way we can update the cross server links in our views, sp etc withouth having to go into each view and sp?
Thanks,
Eloise
thx sir
Thanks pinaldave for a great blog! So much useful information you’ve gained through hard experience you share with us all!
Here’s a note I would like to add to this article. Do not use square brackets in your target column name or it will put them in there literally! For example:
sp_rename ‘[my table].[my col]’, ‘[my col2]’, ‘COLUMN’
is WRONG! Instead say:
sp_rename ‘[my table].[my col]’, ‘my col2’, ‘COLUMN’
And what to do if you have columns with brackets that need renaming? You have to use 2 brackets up front and 3 brackets behind!!
sp_rename ‘[my table].[[my col2]]]’, ‘my col2’, ‘COLUMN’
Another thing, I figured out this syntax by trial and error (on SQL Server 2008.) It also is needed when doing an aggregate on a column with brackets in its name:
select SUM([[my col2]]])
Thank you very much for the “2 brackets up front and 3 brackets behind” discovery!
The square brackets are actually not needed.
Another example when google “sp_rename”:
EXEC sp_rename ‘Sales.SalesTerritory.TerritoryID’, ‘TerrID’, ‘COLUMN’;
thank u……….
Can we change the name and the data type of a column at the same time. I know that we can change the data type first and then rename the column. Can we club these 2 actions together in SQL Server 2005?
Pinal Sir,
Thank you.
Regards,
Sandhya M. Potdar.
thank u sir,
It is easy to learn.