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:
CREATE TABLE Employee ( ID int, FName varchar(50) ) GO EXEC sp_rename 'Employees.FName', 'FirstName', 'column'
After executing the script above, you will receive the following informational message:
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:
Msg 15336, Level 16, State 1, Procedure sp_rename, Line 444 Object 'Orders.OrderAmount' cannot be renamed because the object participates in enforced dependencies.
To drop the constraint, use the following example:
ALTER TABLE Employee DROP CONSTRAINT pk_employee
Popular search terms:
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.
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
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
how to rename a table in sql?
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).