Rename Column

In order to rename a column name, you must use sp_rename. The syntax of sp_rename is the following:

exec sp_rename ‘tablename.ColumnName’, ‘NewColumnName’, ‘column’ –objecttype

Here is an example:

[cc lang=”sql”]
CREATE TABLE Employee
(
ID int,
FName varchar(50)
)
GO

EXEC sp_rename ‘Employees.FName’, ‘FirstName’, ‘column’
[/cc]

After executing the script above, you will receive the following informational message:

rename_column1
This comes up as a reminder that the rename you just performed may have a negative effect on any stored procedures referencing the column. Make sure to check to see if any stored procedures are referencing the column prior to renaming it.

If the column has a constraint, you must drop the constraint prior to renaming the column. Otherwise you will get the following error message:

[code]
Msg 15336, Level 16, State 1, Procedure sp_rename, Line 444
Object ‘Orders.OrderAmount’ cannot be renamed because the object participates in enforced dependencies.
[/code]
To drop the constraint, use the following example:
[cc lang=”sql”]
ALTER TABLE Employee
DROP CONSTRAINT pk_employee
[/cc]

7 comments
Suyog Swami 26 Mar 2015 at 2:10 am

Hello,

Is there a query to rename a column in SQL Server 2008 rather than using a stored procedure ? I have been using the following alter queries but those does not work for me.

Alter table tblPerson alter column Gender GenderId int
Alter table tblPerson alter column Gender set GenderId

Please help…..

Don Schaeffer 11 Nov 2010 at 7:51 am

I have been able to rename columns regardless of their membership in primary or foreign keys without issue. However, I received the message “cannot be renamed because the object participates in enforced dependencies” for a column that had a check constraint.

Kashyap 10 Aug 2010 at 12:16 am

hai
is there any other process to change the name of the column by using alter or update i know the process of sp_rename i want to know the code using alter and update

Derek Dieter 10 Aug 2010 at 9:13 pm

Hi Kashyap,

With SQL Server, the only way to change the column name is to use sp_rename. This can be verified by doing a trace when changing a column name using SSMS.

Derek

subbu 09 May 2010 at 10:18 pm

how to rename a table in sql?

Derek Dieter 10 May 2010 at 3:32 pm

Hi, you have to use sp_rename:

EXEC sp_rename ‘dbo.OldTableName’, ‘NewTableName’

Realize however that any object that reference this table will no longer work. (i.e. stored procedures, views).

Venkata swamy 23 Apr 2014 at 8:32 am

without “using sp_rename” can’t we change the column name ? only using a query, not in database explorer and right click on column name and renaming it.

Featured Articles

 Site Author

  • Thanks for visiting!
css.php