Rename a Table Column in SQL Server

In SQL Server, you can rename a table column using the `sp_rename` stored procedure. This is useful when you need to change the name of a column for various reasons, such as rebranding or reorganization.

Syntax

The basic syntax for renaming a table column is:

EXEC sp_rename 'table_name.old_column_name', 'new_column_name', 'COLUMN';

Where:

  • `table_name` is the name of the table that contains the column you want to rename.
  • `old_column_name` is the current name of the column.
  • `new_column_name` is the new name you want to assign to the column.

Example

Let's say we want to rename the `EmployeeID` column to `EmployeeCode` in the `Employees` table:

EXEC sp_rename 'Employees.EmployeeID', 'EmployeeCode', 'COLUMN';

After executing this command, the `EmployeeID` column will be renamed to `EmployeeCode`.

Important Notes

Before renaming a column, make sure to:

  • Take a backup of the database to ensure data safety.
  • Stop any active connections to the database.
  • Update any references to the old column name in your applications or scripts.

Rename a Table Column Using SQL Server Management Studio (SSMS)

You can also rename a table column using SQL Server Management Studio (SSMS). Here's how:

  1. Open SSMS and connect to the server instance that hosts the database.
  2. In the Object Explorer, expand the database and table that contains the column you want to rename.
  3. Right-click on the column and select Rename.
  4. In the Rename Column dialog box, enter the new name for the column and click OK.

Result

After renaming the column, you can verify the new name by checking the table structure in SSMS or by running the following query:

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Employees' AND COLUMN_NAME = 'EmployeeCode';

This will return the new name of the column, which should be `EmployeeCode`.