SQL Server: Rename Columns or Tables Using SP_RENAME

Renaming a table or column is a common requirement when working with SQL Server. In SQL Server, the `sp_rename` procedure can be used to rename tables and columns. This article shows how to rename a column, rename a table, and change the definition of a column using `ALTER TABLE`

Rename a Column

To rename a column in SQL Server, use `sp_rename` with the `COLUMN` object type.


EXEC sp_rename 'TABLE_NAME.COLUMN_NAME', 'NEW_COLUMN_NAME', 'COLUMN';


For example:


EXEC sp_rename 'dbo.Employee.EMP_NAME', 'EMPLOYEE_NAME', 'COLUMN';

The schema name can be included in the existing column name, which is recommended when specifying the object.
 

Change the Data Type or Size of a Column

`ALTER TABLE ... ALTER COLUMN` is used to change the definition of a column. It does **not** rename the column.

For example:


ALTER TABLE Employee
ALTER COLUMN EMP_NAME VARCHAR(100);

 Rename a Table

To rename a table, use `sp_rename`:

EXEC sp_rename 'dbo.Employee', 'New_Employee';

The existing table name should include the schema when appropriate, while the new table name should be specified without the schema.

 About sp_rename

`sp_rename` changes the name of a user-created object in the current database. It can be used to rename tables, columns, indexes, and other supported objects.

 Remarks

Changing any part of an object name can break scripts and stored procedures. Microsoft recommends not using `sp_rename` to rename stored procedures, triggers, user-defined functions, or views. Instead, drop the object and re-create it with the new name.

Renaming a table or column does not automatically update references to that object. Any dependent queries, views, triggers, stored procedures, or other objects may need to be updated manually. You can use `sys.sql_expression_dependencies` to identify dependencies before renaming an object.

To rename objects, columns, and indexes, you need `ALTER` permission on the object. To rename user-defined types, `CONTROL` permission on the type is required. Renaming a database requires membership in the `sysadmin` or `dbcreator` fixed server roles.