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
That is winning info!
Hi Pinal,
If we change the cloumn name, suppose if the column has a index on it, will the change of name effect the index already created.
hi,
This is use full info
If the column have a constraints how can rename the column
You need to disable the contraints, renmae and enable it
If u r having constraint also, U can use the same syntax. It will work
CREATE TABLE Employee
(
Empid int not null
,Empname varchar(20)
)
alter table Employee add constraint pk_simple primary Key(empid)
sp_rename ’employee.empname’,’EmployeeName’,’column’
Thanks for the info.
Will u guide me how to alter existing constraint
Hi,
Yes if we have not null constraint in column we can able to change the column name.
Hi,
Thanks it works.
Hi Guys,
This is very Useful Information.Thank you.
without using Sp_Rename Stored procedure how can i
rename the table?
Is any other Query to do this?
You need to make use of Management studio
Thank you for sharing. This is a great glob post. Great info and rendering (live the screens etc).
thnq very much sir………
Thank you sir for giving a valuable information
Thank you very much sir for giving a valuable information
I read your articles it is fabulous!!!!
pls try to understand following coding in sql server 2005
SELECT C.NAME,C.ADDRESS,SUM(O.QUANTITY*P.PRICE)
FROM MYCUSTOMER C JOIN ORDERS O
ON C.NAME=O.NAME JOIN PART P
ON O.PARTNUM=P.PARTNUM
Its giving error:–column ‘MYCUSTOMER.NAME’ is invalid in the select list because it is not contained in either an aggregate function or the group by clause
I tried many thing but nothing works
plz look into it…..
thanks in advance!!
— You must add the “Group By” clause inorder to use the Sum Function
SELECT C.NAME,C.ADDRESS,SUM(O.QUANTITY*P.PRICE)
FROM MYCUSTOMER C JOIN ORDERS O
ON C.NAME=O.NAME JOIN PART P
ON O.PARTNUM=P.PARTNUM
Group By C.NAME,C.ADDRESS
Cheer,
Momo
Glory be to God!
if u r using aggreage function, then the columns other than the one u use the aggreagate function should be grouped used group by clause
group by c.name, c.address resolve ur issue
you are using aggregate function in your query so plz use group by C.NAME,C.ADDRESS in the end, it will work.
Thanks for sharing you knowledge Sandeep.
Alam,
Just add GROUP BY C.NAME,C.ADDRESS at the end and try.
All the best…
Sree sir,
or Anyony
plz tell me how I rename the 2 or more than column name at a one time from a one query.
You need to use Management studio. It can’t be done in a single query
thanx a lot it works fine
thanks SREE…….
GROUP BY clause has to be used since c.name and c.address columns have one to many relationship with other columns.
This is not supported going forward. Avoid using SP_RENAME where possible or you will have to edit your code when you upgrade to SQL 2008..
hii….bhai tera bahut bahut dhanyawad….
hi,
i want to rename the table column using sql queries. can anyone help me ?
Thanks,
Ramesh
Hi,
Even I am in search of the solution for the same requirement.
could u please share the same with me once got the solution.
Thanks in advance
SP_RENAME ‘TABLENAME.COLUMNNAME’,’NEW COLUMN NAME’
sp_RENAME ‘Table_First’, ‘Table_Last’
SP_RENAME ‘TABLENAME.COLUMNNAME’,’NEW COLUMN NAME’,’COLUMN’
hi
thanx sir this is very nice