How to Change Column Name in SQL?

It may often happen that, we see a requirement to change the name of the column in the database to serve their purpose. With the help of SQL or Structured Query Language, the Database Administrators store, update, manipulate and retrieve data in relational databases. So, in this article let us understand how to rename a column name in SQL.

The following topics will be covered in this article:

1. 1. What is SQL?
   2. What is RENAME command in SQL?
   3. Rename column name in MySQL, MariaDB, Oracle and PostgreSQL
   4. Rename column  name in MS SQL Server
   5. Rename table name  MySQL, MariaDB, Oracle

So let us get started then,

What is SQL?

SQL or most commonly known as SEQUEL is a Structured Query Language used for managing and accessing the databases. It is based on the English language and is designed in a way to easily retrieve, manipulate and access the data. If you wish to know more about the basics of SQL in-depth, you can refer to the article on SQL Basics. In SQL, there are various statements/ commands used to manipulate data. One such manipulation, which is quite popular in databases is renaming the column name in SQL. 

So, let us understand how to use the RENAME command in SQL.

What is RENAME command in SQL?

This command is used to change the name of a column to a new column name. It is also used to change the table to a new table name. Let us understand how to use this command in different databases. But, before that, let us consider the following table to understand all the examples:

How to Rename column name in SQL?

How to Change Column Name in mysql, MariaDB, Oracle and PostgreSQL

To rename a column name in MySQL, MariaDB, Oracle, and PostgreSQL, you can follow the below syntax:

Syntax

ALTER TABLE TableName
RENAME COLUMN OldColumnName TO NewColumnName;

Example:

Write a query to rename the column name “BID” to “BooksID”.

ALTER TABLE Books;
RENAME COLUMN BID TO BooksID;

For a detailed, You can even check out the details of relational databases, functions, and queries, variables, etc with the SQL Training.

On executing the above query, you will see the following output:

Output:

You can also use the CHANGE keyword to rename a column name as follows:

Syntax

ALTER TABLE TableName
CHANGE COLUMN OldColumnName NewColumnName Data Type;

Example:

Write a query to rename the column name “BID” to “BooksID”.

ALTER TABLE Books;
CHANGE COLUMN BID BooksID INT;

On executing this query, you will see the output the same as the above output.

How to Rename Column Name in SQL Server

The process of renaming column name is MS SQL Server is different when compared to the other databases. In MS SQL Server, you have to use the stored procedure called sp_rename.

Syntax

sp_rename 'TableName.OldColumnName', 'New ColumnName', 'COLUMN';

Example:

Write a query to rename the column name “BID” to “BooksID”.

sp_rename 'Books.BID', 'BooksID', 'COLUMN';

The resulting output will be the same as that for the above queries. Now, that you have understood how to rename a column name in various databases, let us see how you can rename a table name.

Rename table name MySQL, MariaDB, Oracle

To rename a table name, you can use the RENAME command in SQL, in the following manner:

Syntax:

ALTER TABLE OldTableName
RENAME TO NewTableName;

Example:

ALTER TABLE Books
RENAME TO ListOfBooks;

Now, if you execute the below query to see the details in the table  ListOfBooks, you will see the following output:

Query:

SELECT * FROM ListOfBooks;

With that, we come to an end to this article on Rename column name in SQL. I hope you found this article informative. I hope you understood how to use the above commands. If you wish to learn more about MySQL and get to know this open-source relational database, then check out our MySQL DBA Certification Training which comes with instructor-led live training and real-life project experience. This training will help you understand MySQL in-depth and help you achieve mastery over the subject.

Got a question for us? Please mention it in the comments section of this article on “Rename column name in SQL” and I will get back to you.