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
thnx for giving exact info ….
Thanks for giving the solution ….
Can we know related store procedures also
THANKU GENIUS
Thank for sharing….
Hello, I used the sp_rename to rename my column name like:
sp_RENAME ‘copyTableTo.[date]’ , ‘[date1]’, ‘COLUMN’
But now when I wants to change my column name from “date1” to “date” like:
sp_RENAME ‘copyTableTo.[date1]’ , ‘[date]’, ‘COLUMN’
It is giving following error:
**”Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong.”**
Hi gravit,
Please have a look into a post
Well there is a method for changing the mistakenly rename column ‘[date1]’ to date . Just simple you need to go to the table and then click plus sign of the table and then you can see the list of columns of table then right click and add New Column tab which will edit all the list of columns and there you go just change the ‘[date1]’ to date . This will solve the issue we are having.
Thanks,
Anil Maharjan
If you need to rename a column in a stored procedure and your working with a temp table. This worked for me!
exec (‘use tempdb; EXEC sp_rename ”#tempRpt3.[YEAR]”, ”QUESTION”, ”COLUMN” ‘)
I have a Column name like ” [year] ” open bracket Year close bracket .
I tried to rename the this column into Year with below query
EXEC sp_rename
@objname = ‘Student.[Year]’,
@newname = ‘Year’,
@objtype = ‘COLUMN’
and I am getting below error
Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong.
tried to drop that column but getting error while drop the column
anyone suggest me how to delete or drop that column
Thanks,
Karna
Got Soluation with below code.
EXEC sp_rename
@objname = ‘Student.[[Year]]]’,
@newname = ‘Year’,
@objtype = ‘COLUMN’
Great work
Thank U Very Much Sir
I need to submit my address essential query in sql server as soon as possible
sp_RENAME ‘TableName.[OldColumnName]’ , ‘[NewColumnName]’, ‘COLUMN’ –> is incorrect
Please change this as it will insert square brackets [] as part of the the new column name.
After using this sp_RENAME ‘TableName.[OldColumnName]‘ , ‘[NewColumnName]‘, ‘COLUMN’
if we again rename the coulmn it gives error:
Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong.
why?
The above Article was very useful…
Review the following link when renaming the Columns again …
hi , how to get the log record of objects which has been renamed in sql server
OSOM
there is a problem with the rename column script mentioned here. It adds square braces in the renamed column.
Thank you, very useful article!
Thank you, to you i found what i needed.