How to Rename Column Name in MS SQL Server

Sometimes we need to rename a column name of a table in our MS SQL Server database. It is very much easy to change or rename column name in MS SQL Server. Just you need to write statements as formatted below and execute:

USE database_name;
EXEC sp_rename 'targeted_table_name.old_column_name', 'new_column_name', 'COLUMN';

In this syntax:

  • First, select the database where your table exist by USING database_name statement.
  • Second, write EXEC statement and then write sp_rename.
  • Third, write single quotation then table name then write dot then write old column name ending with single quotation, then new column name with single quotation and lastly write ‘COLUMN’
  • Fourth, you have to follow structure as mentioned above.

Let’s create a table for a demonstration:

CREATE TABLE [DataAnalytics].[dbo].[tbl_customer] (
  customer_id long IDENTITY(1,1) PRIMARY KEY,
  name NVARCHAR(50) NULL,
  o_mail NVARCHAR(30) NOT NULL
);

tbl_customer table has a o_mail column which need to be renamed. Now you want to change the column o_mail as email. The following statement will rename the column name:

USE DataAnalytics;
EXEC sp_rename 'tbl_customer.o_mail', 'email', 'COLUMN';

I have an another tutorial related to this tutorial where you will learnHow to Drop Columns in MS SQL Server.

In this tutorial, I have shown you how to rename a column name of existing table in SQL Server by executing built-in sp_rename stored procedure. Hope you have enjoyed the tutorial. If you want to get updated, like my facebook page http://www.facebook.com/freetechtrainer and stay connected.

Add a Comment