Rename column SQL Server 2008
I am using SQL Server 2008 and Navicat. I need to rename a column in a table using SQL.
ALTER TABLE table_name RENAME COLUMN old_name to new_name;
This statement doesn't work.
EXEC sp_RENAME 'TableName.OldColumnName' , 'NewColumnName', 'COLUMN'
Documentation: sp_rename (Transact-SQL)
For your case it would be:
EXEC sp_RENAME 'table_name.old_name', 'new_name', 'COLUMN'
Remember to use single quotes to enclose your values.
Read more... Read less...
SQL, you can do this in Microsoft SQL Server Management Studio. Here are a few quick ways using the GUI:
Slow double-click on the column. The column name will become an editable text box.
Right click on column and choose Rename from the context menu.
This way is preferable for when you need to rename multiple columns in one go.
- Right-click on the table that contains the column that needs renaming.
- Click Design.
- In the table design panel, click and edit the textbox of the column name you want to alter.
NOTE: I know OP specifically asked for SQL solution, thought this might help others :)
EXEC sp_rename 'TableName.OldName', 'NewName', 'COLUMN'
You should also specify the schema of the table or you might get this error:
Msg 15248, Level 11, State 1, Procedure sp_rename, Line 238 Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong.
If it is a deployment script I would also recommend adding some additional security to it.
IF EXISTS ( SELECT 1 FROM sys.columns WHERE name = 'OldColumnName' AND object_name(object_id) = 'TableName' ) AND NOT EXISTS ( SELECT 1 FROM sys.columns WHERE name = 'NewColumnName' AND object_name(object_id) = 'TableName' ) EXEC sp_RENAME 'SchemaName.TableName.OldColumnName', 'NewColumnName', 'COLUMN';
It would be a good suggestion to use an already built-in function but another way around is to:
- Create a new column with same data type and NEW NAME.
- Run an UPDATE/INSERT statement to copy all the data into new column.
- Drop the old column.
The benefit behind using the
sp_rename is that it takes care of all the relations associated with it.
From the documentation:
sp_rename automatically renames the associated index whenever a PRIMARY KEY or UNIQUE constraint is renamed. If a renamed index is tied to a PRIMARY KEY constraint, the PRIMARY KEY constraint is also automatically renamed by sp_rename. sp_rename can be used to rename primary and secondary XML indexes.
You can use
sp_rename to rename a column.
USE YourDatabase; GO EXEC sp_rename 'TableName.OldColumnName', 'NewColumnName', 'COLUMN'; GO
The first parameter is the object to be modified, the second parameter is the new name that will be given to the object, and the third parameter COLUMN informs the server that the rename is for the
column, and can also be used to rename
alias data type.