How to rename a column in SQL Server?
You have several ways to rename a column in SQL Server:
1. Using SQL query editor:
In SQL Server, you can use sp_rename
to rename almost every user-created objects in the database, including database, table, index, column, alias data type, etc.
- The general syntax for
sp_rename
:
sp_rename [ @objname = ] 'object_name' , [ @newname = ] 'new_name'
[ , [ @objtype = ] 'object_type' ];
- In this case, we are renaming a column, so the SQL statement should be:
EXEC sp_rename 'table_name.old_col_name', 'new_col_name', 'COLUMN'.
For example, we can rename the column address
from the table student
to full_address
using this query:
EXEC sp_rename 'students.address', 'full_address', 'COLUMN';
You can do the same with table, database, index, object, etc.
For example, to rename a table in the database:
EXEC sp_rename 'schema_name.old_table_name', 'new_table_name';
However, as you execute the statement changing the name for a column, you will probably receive a warning that changing any part of an object name can break scripts and stored procedures.
So it’s not recommended to use this sp_rename
statement to rename stored procedures, triggers, user-defined functions, or views; you should drop the object and re-create it with the new name instead.
2. Using TablePlus GUI Tool
In TablePlus, you can be able to edit the table structure and rename the column easily without having to write queries. The following steps will help:
- After connecting to the database, select the table from the left panel.
- Switch to the structure tab (find the button at the bottom of the window).
- Double click on the column name and edit it. You can also select the column and edit using the right toggle.
- Hit Cmd + S to commit changes to the server. Or you can click on the Commit button near the top left.
All done!
Need a good GUI Client to work with MS SQL Server? TablePlus provides a modern, native tool with intuitive UI to manage multiple databases in cluding SQL Server, MySQL, PostgreSQL, SQLite, Oracle…
And it’s available for free!
Not on Mac? Download TablePlus for Windows.
On Linux? Download TablePlus for Linux
Need a quick edit on the go? Download TablePlus for iOS