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
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 (http://blog.SQLAuthority.com)
pinaldave
That is winning info!
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.
hi,
This is use full info
If the column have a constraints how can rename the column
Thanks for the info.
Will u guide me how to alter existing constraint
Hi,
Yes if we have not null constraint in column we can able to change the column name.
Hi,
Thanks it works.
Hi Guys,
This is very Useful Information.Thank you.
without using Sp_Rename Stored procedure how can i
rename the table?
Is any other Query to do this?
[...] 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 [...]
Thank you for sharing. This is a great glob post. Great info and rendering (live the screens etc).
thnq very much sir………
Thank you sir for giving a valuable information
Thank you very much sir for giving a valuable information
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!!
Alam,
Just add GROUP BY C.NAME,C.ADDRESS at the end and try.
All the best…
thanx a lot it works fine
thanks SREE…….
GROUP BY clause has to be used since c.name and c.address columns have one to many relationship with other columns.
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..
hii….bhai tera bahut bahut dhanyawad….
hi,
i want to rename the table column using sql queries. can anyone help me ?
Thanks,
Ramesh
hi
thanx sir this is very nice
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?
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
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 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.