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
Nice information…Thank u
i get my problem solved here thnx 4 it
Just what I needed, thanks :D
Hi Thanks !!!!
sp_rename ‘Mkt_Mart_vw.flat_site1′,’@tablename’
does not seem to work..
the new tablename is basically a parametrized table name to be input by user
Hello Sriniwas,
Following code is working:
declare @name varchar(100)
set @name = ‘tblNewName’
exec sp_rename ‘dbo.tblName’, @name
Regards,
Pinal Dave
Hi Pinal,
I tried with the same sp_rename but it gave error to me. Can you tell query to rename table name, I want to rename my table as ABC2 from ABC1, what quey should be written?
2. How I can modify CHECK constarint by query, I had written
Create table ABC1
(Dept_ID int NOT NULL Primary key check(Dept_ID > 10), Dept_Name varchar(15) ),
Now i want to update it as check(Dept_ID and not 0 <10)
Can you please help me
Thanks
Jignesh
Thanks so much. I can always count on you to come through when I need some help. Keep up the good work!
If you rename a table, what happens to the constraints, triggers, etc?
Hi…..
Thanks you very much, it has save my lot of time :-)
Does sp_rename depend on how huge the table and data is? Would it take the same amount of time if I did the rename of a columns containing 1 row and 10 million rows?
I tested it and it is instantaneous
As always you contribute GREAT SQL stuff, Pinal!
thanks!
Thankyou sir
Hello srinivas,
Your doing great job.It’s really very very useful for all the developers..
Thanks a lot srinivas.
Thanx for the info….
I have 2 questions :
1. Suppose we have 20 tables which all of them have got the same columns (the number of the columns and the name of the columns are all same ). How can I combine and convert all of them into a single Table.
2. Imagine we have the same case as explained above( 20 tables with same columns ), so we want to change the name of each tables to the same name which is in first row of Third column of each table.
any one have got some codes or solution for that ?
Thx
1) use select into. you need to define the relation between all tables which you can use in your where condition.
So lets assume you have 3 table with same columns and …….
then select a.*(or any perticualr clolumn name), b.*,c.*
where a.id=b.id(whatever matching column) and a.id = c.id(again matching column) into temp
from table1 a, table2 b, table3 c
you can modify matching and column name as per your requirement.
2) Lets say you have third column name as ‘name’
then
declare @name as varchar(250)
select top 1 @name = name from table 1
sp_rename ‘table1’, ‘@name’
Now again, how to get the requried top 1 is depend as per your requirement. I have just assumed that whatever comes in top without sorting or filtering
just a bit modification…..
just replace this line
sp_rename ‘table1′, ‘@name’
with
exec sp_rename ‘table1′, ‘@name’
i mean
sp_rename ‘table1′, @name
no codes are requried with variable as its already varchar type
Dear Ashish :
Thank you so much . Im gonna try ur solution. If any question regarding this issue ,shall I write here or just mail to you ?
thx
nice to hear it worked.
just post here because:-
1) it will be have more visibility and more quick chance to reply
2) I have configured this blog in my office mail so I will get it in my office mail box automatically whenever there is new item added.