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 a lot.
thankssss
thanx…
thanks @ajay
i want to ask a question ?
can we extract the characters from a query based on there position ?
for Example : There is a string ‘ABCDEFGHIJKLMNOPQ’
I want to Extract character at even postion.
while rename a column i have given [ ](bracket while rename a column) so it resulted with the bracket under that column like ‘[price]’. how to rename it as just ‘price’
sp_rename ‘pricing.[price ]’ , ‘[price]’
sp_rename ‘pricing.[price]’ , ‘price’
this above query is not working.
sp_rename ‘pricing.[price ]’ , ‘[price]’
due to above query the column is showing with [ ] eg..'[price]’. How to change it into ‘price’
means without bracket
sp_rename ‘pricing.[price ]’ , ‘price’
this query is not working, kindly advice me
thanks
Use Double quotes to rename it without braces:
EXEC SP_RENAME ‘pricing.”[price ]”‘, ‘price’,’COLUMN’
sp_RENAME ‘TableName.[OldColumnName]’ , ‘[NewColumnName]’, ‘COLUMN’
Square brackets should not be used around NewColumnName. If used Column name will have [] which causes a lot of confusion while joining the table.
Thanks. It was very helpful to me
If rename a columna name.
Caution: Changing any part of an object name could break scripts and stored procedures.
How search column name in stored procedures, triggers, etc ?
hai sir,
i Renamed the table name with following command
sp_RENAME ‘[OldTableName]’ , ‘[NewTableName]’
i got messege
Caution: Changing any part of an object name could break scripts and stored procedures.
But table name is changed
after that
i Executed table with newtable name
am getting invaled object in u r Database
but table is showing in database design
please give reply as soon as possible
Make sure to have removed brackets [ ] before exec new name script. If you are using a saved query to execute the table that has had the name altered make sure to reflect this change within the query script.
Hi, I would like to know how can I have certification for Advanced SQL. And where can I get reading materials to prepare for Adv. SQL
thanks!
Hi, quite useful article, but I think you should change example, from “sp_RENAME” to “sp_rename”. The procedure is lowercase.
Normally you will not encounter any problems, but when I was running this against DB with CS collation I was getting error that procedure not exists. It took me “few” minutes, before I realised that it was just because I use “sp_RENAME” instead of “sp_rename” :)
Nice catch.
what happens if I run the same sp_rename over a column multiple times? Is the command idempotent?
Hi Pinal,
Does this sp_rename will be logged into default trace ?
Hi Pinal,
Is it possible to change name of column in the VIEW instead of TABLE with this query ?if not, whats your suggestion to change column name of VIEW for 1000 VIEWS?
sp_RENAME ‘VIEW Name.[OldColumnName]’ , ‘[NewColumnName]’, ‘COLUMN’
Instead of renaming column, you can alter the view and use different alias name for the column that you want to rename
How to rename multiple columns in a table using sp_rename ?
How to rename multiple columns in a table using sp_rename ?
Just EXEC SP_Rename multiple times. Dooh =) Too many? Build some Dynamic SQL
This didn’t specifically solve my issue, but it got me thinking. I was using brackets around the schema and table names. SP_RENAME wasn’t working because what I was sending looked right, but wasn’t. When I did a simple select against the table I found it was schema.[[tablename]] but it looked like schema.[tablename]. I went back and undid the brackets and everything works now. Enjoyed the detail thinking you provided.