How to Rename Column name in SQL Server

by

In this SQL Server tutorial, you will learn how to rename column name in SQL Server.

Sometimes, a situation arises due to changes in the business logic where you will have to change the column’s name in the table.

Considering that situation, I have explained two methods in this tutorial for renaming a column in your table.

The first method will walk you through the query steps that you can use to rename columns, and the second will show you through SQL Server Management Studio.

Rename Column name in SQL Server

To rename column names in SQL Server, you can use Transact-SQL or SQL Server Management.

Let’s start with Transact-SQL. Here, you must use the stored procedure sp_rename to rename a column name in a table.

The syntax is given below.

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

Where,

  • EXEC sp_rename: It means calling or executing the sp_rename procedure to rename the table column name.
  • table_name.old_column_name: there table_name is the name of the table containing the column, and old_column_name is the name of the column you want to change.
  • new_column_name: Name of the new column that you want to keep.

With an example, let’s see how to rename the column name.

First, create a table named Products with columns id and productName using the command below.

CREATE TABLE Products(
id INT ,
productName VARCHAR(255)
);

SELECT * FROM Products;
Creating Table with Columns to Rename Column name in SQL Server

As you can see, the table contains two columns: id and productName. Suppose you want to change the column name from id to product_id, which is more meaningful. To do so, you can use the sp_rename stored procedure using the command below.

EXEC sp_rename 'Products.id', 'product_id', 'COLUMN';

SELECT * FROM Products;
Rename Column name in SQL Server

The output shows that the column’s name changed to product_id. Similarly, you can change the name of any column in a table.

This is how to rename a column in SQL Server using the sp_rename stored procedure.

Next, let’s see how to change the column name using SQL Server Management Studio.

Rename Column name in SQL Server using SSMS

To rename column names in SQL Server using SSMS, go to the Object Explorer section in your SQL Server Management Studio.

First, expand the Databases, then expand the database where your table exists; in my case, I have created the table in the master database, so I am expanding that. After that, expand the Table folder to show all the tables.

Next, expand the table that contains the column, and finally, expand the Columns folder to view all the columns of that table, as shown below.

Finding Column to Rename Column name in SQL Server using SSMS

Now, choose the column whose name you want to change, right-click on it, and select the Rename option to rename the column. Look at the image below.

Choosing Rename Option to Rename Column name in SQL Server using SSMS

For example, I selected the productName column and changed its name to product_name, as shown in the image below.

Rename Column name in SQL Server using SSMS

After changing the column name, you see the new column name as in the above image.

I hope you understand how to rename column name in SQL Server through Transct-SQL and SQL Server Management Studio.

Conclusion

In this SQL Server tutorial, you learned how to rename column name in SQL Server using the Transact-SQL and SQL Server Management Studio.

In Transact-SQL, you have used the sp_rename stored procedure to rename the column name of any table.

You may like to read: