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 , I renamed a table by mistake using the F2 key in SSMS and forgot the old table name :(. Is there any way I could get the old name or undo the rename?
I would really really appreciate any help.
If the server’s version is 2005 or later, try looking ar modify_date column from sys.tables
Hi,
i did check the sys.tables. It has the new table name and not the original table name.
thank you so much sir its really very helpful to the learners
Any idea how can I run it for multiple column name updates … I was trying to use excel to update the SQL, and then run it at once, but it gives an error (they work fine separately) :(..
Trying something like
sp_RENAME ‘[IRIOnly].Geography’ , ‘Location’, ‘COLUMN’
sp_RENAME ‘[IRIOnly].PriceCat’ , ‘SKU’, ‘COLUMN’
sp_RENAME ‘[IRIOnly].Brand-adj’ , ‘Brand’, ‘COLUMN’
sp_RENAME ‘[NetList].PriceCat’ , ‘SKU’, ‘COLUMN’
Any help will be appreciated..
When you execute multiple statements you need to use EXEC command
EXEC sp_RENAME ‘[IRIOnly].Geography’ , ‘Location’, ‘COLUMN’
EXEC sp_RENAME ‘[IRIOnly].PriceCat’ , ‘SKU’, ‘COLUMN’
EXEC sp_RENAME ‘[IRIOnly].Brand-adj’ , ‘Brand’, ‘COLUMN’
EXEC sp_RENAME ‘[NetList].PriceCat’ , ‘SKU’, ‘COLUMN’
Brother, help my in script !!!
Locate error the script, please …
select ‘exec sp_rename “‘+table_name+'”,”‘+substring(table_name,1,3)+’990’ +'”‘ from information_schema.tables
Tank’s
Try this
select ‘exec sp_rename ”’+table_name+”’,”’+substring(table_name,1,3)+’990′ +”” from information_schema.tables
Hi Pinal,
This is my first comment on your blog so let me start with thanking you for tremendous blog, of your’s, that has saved countless hours of head banging for me.
So here’s my question..
One of our DBAs has accidentally renamed a table by clicking on that in SSMS. We do have a backup of the database to retrieve the DB and getting the table name restored. But I was wondering if there was a quick UNDO option for name changes like these.
There is no quick way of undoing the changes. You should the original name from old backup
thank you very much sir…
sir how we can change the temporary table column name???
its useful..
sp_RENAME ‘[department_info]’ , ‘[maintenence.info]’
after that i am not able to view below query
select * from maintenence.info
ALTER TABLE table_name
RENAME COLUMN old_name to new_name;
Hello Pinal,
I have list of tables like A,B,C,D,E..
All this tables i have to rename with different names
e.g.
SP_Rename
‘A’ to ‘AB’,
‘B’ to ‘BC’,
‘C’ to ‘DE’,
‘D’ to ‘DE’
Is it Possible with SP_Rename?
Urgent Reply please i m in big trouble..
Thanks in advance..
Hi PinalDave,
My question was ” how to rename the multiple tablenames as dynamically”.
See for example my db having five tables with same structure and these tables contails data also: temp_site1,temp_site2,temp_site3,temp_site4 and temp_site5.
I need to change all table name as ” site1,site2,site3,site4 and site5 “. Is any query or some other way to solve.
Thanks in advance….
This is a evergreen site .It is beneficiary for all user
Hi,i I want to know that the message “caution :Changin g any part of the object can break the script ” would be there any impact if the data is very large.
Ya is der any constraints related to that column you shd change there also or else it will cause some issues.
thanks .. it helped me..
Hi,
Thanks it works.
your’s every syntax in sql is very good and simple for remembering. Thanks u very much.
sp_RENAME ‘TableName.[OldColumnName]’ , ‘[NewColumnName]’, ‘COLUMN’
this works great if you want brackets around your new column name
tnx brother
thanks,it would be great help for me