How to use the sp_rename System Stored Procedure In SQL

To rename a column of a database in SQL Server, we can use the sp_rename system stored procedure. The sp_rename procedure is a built-in system stored procedure that allows the users to rename various database objects like tables, columns, views, and indexes. Following is the syntax to use the sp_rename system stored procedure:

Syntax:

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

where:

• table_name: is the name of the table containing the column you want to rename.
• old_column_name: is the current name of the column.
• new_column_name: is the desired new name for the column.
• COLUMN: is an additional parameter that directs the system procedure to rename the column.

Example

To change the column name from State to Residential State of the above table Customers we will have to run the following query

Query

EXEC sp_rename 'Customers.State, 'Residential State', 'COLUMN'

Output:

Explanation: The EXEC sp_rename command renames the State column in the Customers table to ‘Residential State‘. The resulting table schema reflects this change, displaying the new column name ‘Residential State‘ instead of State, with all data remaining intact.

SQL Server is a widely used Relational Database Management System (RDBMS) that allows users to create and manage databases effectively. Renaming a column in a database is a common task usually required when users want to change the database schema. In this article, we will explore different methods to rename columns in SQL Server.