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
Thanks for this. You’re da man
Hi Pinal,
I’m new to SQl, and am trying to convert all the column names of a table to UPPERCASE. There are around 80 columns. What is the code for that?
Thanks in advance!
thank u, from me and davesh chaudhary
Dave, your site has been a great help over the past few months as I’m learning new things about Sql Server. I’ve learned many things from you. Thank you very much! I appreciate the easy access to knowledge and hope to contribute back down the road a bit when I’m able.
doing very great job, keep moving
thanks!
Thanq its working and is very usefull for me
Hi
When i am trying to change the column name of the table containing some data its giving me the below caution
Query: sp_rename ‘products1.prodname’,’productsname’
Caution: Changing any part of an object name could break scripts and stored procedures.
what does this caution mean.
Will be waiting for reply.
Thanks in advance
If that column is referred in any procedures, functions,etc, they will be affected so you need to change there too
Madhivanan
Thanks for the reply. But names used in the example ie “products1.prodname’,’productsname’” are not any procedures.
But still its giving me problem.
Please let me know how to change the column name
it is not working, it is not working
it’s take too long time to execute this command..
it worked thank….and there is no need to add COLUMN at last…
Thanks u so much.
Hi Sir,
I understood abt how to rename a column. But I want to know, how can i rename the data type.. Has it to be done before renaming the column or can it be done even after that?
I need to know the query of renaming the data type.
Thanks & Regards,
Ramya
Alter table table_name alter column column_name new_datatype
I renamed a table with sp_rename. It changed the table name but not constraints. The constraints remain with the old table name eg.PK_OldName. Is it can effect the other things.
Thanks. This info was very helpful.
when i m changing the column name in hash table it gives me error like this..is it possible to change column name by using hash table values??
Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong.
Hi! Don’t use hash table to rename, try temp schema if you have like TEMP. then use SP_RENAME, this could help you
EXEC sp_rename'[#Result][Normalized_Domain]’,’Domain’,’COLUMN’
getting error when i execute this;
EXEC sp_rename'[#Result].[Normalized_Domain]’,’Domain’,’COLUMN’
error:
Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong.
Hi Dear,
sp_RENAME ‘TableName.[OldColumnName]’ , ‘[NewColumnName]’, ‘COLUMN’
sp_RENAME ‘[OldTableName]’ , ‘[NewTableName]’
both query will run.
Md Jiaul Islam
Hi,
I am trying to write a code to update a column of the table using the results of an inner join… can anybody help me out??
vihar
Post some sample data with expected result