Rename Column in SQL Server
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.
Rename Column in SQL Server
There are 2 methods through which we can rename columns of a database in SQL Server which are explained below:
- Using the sp_rename system stored procedure
- Using ALTER statement
Let’s set up an environment
To understand how we can rename columns in SQL Server, we will consider the table Customers as shown below:
CustomerID |
CustomerName |
City |
State |
Age |
---|---|---|---|---|
1 |
Amit Kumar |
Mumbai |
Maharashtra |
28 |
2 |
Kavya Sharma |
Delhi |
Delhi |
35 |
3 |
Amit Singh |
Bangalore |
Karnataka |
42 |
4 |
Rohan Kumar |
Kolkata |
West Bengal |
33 |
1. Using the sp_rename System Stored Procedure
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:
CustomerID |
CustomerName |
City |
Residential State |
Age |
---|---|---|---|---|
1 |
Amit Kumar |
Mumbai |
Maharashtra |
28 |
2 |
Kavya Sharma |
Delhi |
Delhi |
35 |
3 |
Amit Singh |
Bangalore |
Karnataka |
42 |
4 |
Rohan Kumar |
Kolkata |
West Bengal |
33 |
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.
2. Using ALTER Statement
To rename the column of a database in SQL Server, we can also use the ALTER table command. ALTER is a Data Definition Language(DDL) command that is used to update the schema of tables, views, and indexes in SQL Server. Following is the syntax to rename the column in SQL Server using the ALTER command:
Syntax:
ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name
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 that you want to rename.
- new_column_name: is the desired new name for the column.
Example
To change the column name from CustomerName to User of the above table Customers we will have to run the following query
Query:
ALTER TABLE Customers RENAME COLUMN CustomerName TO Users
Output:
CustomerID |
Users |
City |
State |
Age |
---|---|---|---|---|
1 |
Amit Kumar |
Mumbai |
Maharashtra |
28 |
2 |
Kavya Sharma |
Delhi |
Delhi |
35 |
3 |
Amit Singh |
Bangalore |
Karnataka |
42 |
4 |
Rohan Kumar |
Kolkata |
West Bengal |
33 |
Explanation: The ‘ALTER TABLE Customers RENAME COLUMN CustomerName TO Users‘ command attempts to rename the CustomerName column to Users. However, SQL Server does not support this syntax; use sp_rename instead for renaming columns in SQL Server.
Conclusion
In conclusion, SQL Server provides efficient tools for renaming columns, primarily using the sp_rename system stored procedure. While ALTER TABLE RENAME COLUMN is a standard SQL syntax in some database systems, SQL Server relies on sp_rename for this operation. Users can effectively manage and update their database schemas using these methods.
Contact Us