How to Rename Column Name in SQL

Introduction

We are surrounded by Data. We used to store information on paper in enormous file organizers. But eventually, we have come to store it online in what we call databases. This is where SQL comes into the picture.  It’s a language that speaks with databases. SQL stands for the structured query language, but it’s usually called SQL or Sequel.

Essentially, it’s a language that helps us communicate with databases that store your data. On the off chance that you need to pull, alter, add data to an information base, you can utilize the language of SQL to do so. By altering data we mean, changing the table information that is created by us, like changing the row name, making change column name in SQL etc. 

Check out our free courses to get an edge over the competition.

Learn to build applications like Swiggy, Quora, IMDB and more

If you’re about how it works, just think about a database as a warehouse, that’s how SQL works. Information tables act like file organizers and the actual information is like stored documents. Data is stored in this warehouse or database, and coding languages like C, C++, or Java are used to build warehouse databases. SQL is built to retrieve, manipulate, and access the data. Renaming a column name or table name is one of the most used commands on databases.

SQL Server permits you to change the column whenever required. You need to rename column in SQL where the column name is meaningless or doesn’t meet its purpose. It should guarantee that ALTER permission is available on the object before you change column name in SQL. If you are wondering how to change column name in SQL server, you first need to be aware of the rules and restrictions.

 Restrictions:

Before you rename column in SQL, you must learn that SQL Server comes with certain restrictions. The reason is whenever you rename a column, all references to that column may not be renamed. So, you should manually change all objects belonging to the renamed column.

Check out upGrad’s Java Bootcamp

What is SQL? 

SQL or structured query language is a programming language that stores and processes data in a relational database. This type of database is used for storing data in a tabular form, using columns and rows, representing the different data attributes as well as the different relations between the data values. 

SQL statements can be used for storing, updating, removing, searching, and retrieving data from the database. This programming language can also be used for maintaining and optimizing the database performance. 

Even though almost all database systems use SQL, most of these systems have additional extensions that can be only used for that particular system. However, standard commands like ‘insert’, ‘select’, ‘rename’, ‘change’, ‘drop’, ‘create’ etc can be used to accomplish several tasks. 

The tables in SQL can be altered according to what you need. Among these, changing column name in SQL is a common practice. Later in this article, we will address how you can update column name in SQL with some of the keywords like ‘rename’ and ‘change’. 

Let’s understand how to change column name in SQL with an example. Suppose you want to change a table’s column being referenced in a trigger. So, the trigger must be modified to show up the changes in the new column name too. Before changing the name, you can use the sys.sql_expression_dependencies to list the dependencies on the object. 

RENAME is a crucial command that is one of the major parts of finding your answer to how to change the column name in SQL. Let’s get into a deeper understanding of how to use the RENAME command in SQL. Once you know about it, you will find out how to rename a column in SQL. 

upGrad’s Exclusive Software and Tech Webinar for you –

SAAS Business – What is So Different?

Check out upGrad: Full Stack Development Bootcamp (JS/MERN)

Learn Software Development Courses online from the World’s top Universities. Earn Executive PG Programs, Advanced Certificate Programs or Masters Programs to fast-track your career.

Creating a table in SQL 

Before we learn how to change column name in a table, let us first learn how to create a table in SQL. To create a table in SQL, we must use the CREATE command. Let us see the syntax and query for it: 

Syntax: 

Line 1: CREATE TABLE table_name (

Line 2:    Column 1 name data_type, 

Line 3:    Column 2 name data_type, 

Line 4: );

 

Query:

Line 1: CREATE TABLE Students (

Line 2:     SID INT PRIMARY KEY, 

Line 3:     SName VARCHAR (20)

Line 4:     Marks INT, 

Line 5: );

Let’s input some data into this table using the INSERT command: 

Line 1: INSERT INTO Students (SID, SName, Marks)

Line 2: VALUES 

Line 3: (1, ‘Harry’, ‘70’),

Line 4: (2, ‘Rick’, ‘50’),

Line 5: (3, ‘Martha’,’80’),

Line 6: (4, ‘Gwen’, ‘60’),

Line 7: (5, ‘John’, ‘75’);

On executing the above query, the result is: 

Table name: Students

SID	SName	Marks
1	Harry	70
2	Rick	50
3	Martha	80
4	Gwen	60
5	John	75

How to Rename Column Name in SQL? 

Without causing any alterations to the datatype, you will be able to change name of column in SQL. Altering a column name can help in maintaining consistency, improving clarity, or including new data requirements. 

In order to change column name in SQL, you will have to use the ALTER TABLE command. It will allow you to modify the structure of an existing table. The specific syntax might vary according to the DBMS in use. 

General steps to change column name in SQL: 

Step 1: Using the correct SQL command-line interface, connect to your database. Make sure that you can perform column name change in SQL existing table structure. 

Step 2: Make sure that the table and column you want to rename are present. 

Step 3: The ALTER TABLE command is used to change column name in SQL. To change a column name, you need to use the appropriate command specific to your database along with ALTER TABLE. 

Step 4: Use the RENAME COLUMN or CHANGE command (according to your DBMS), to notify that you want to rename column name in SQL. Specify the current name of the column and the new name that you want.

Step 5: Once the statement has been created, execute it to rename the column. Make sure that the statement is free of any errors and syntactically correct. 

About the RENAME command in SQL

What is RENAME command in SQL? 

In often situations database administrators prefer changing the name of the table to the relevant one and here comes RENAME command. If you are wondering how to change column name in SQL server, this order is utilized to change the name of a column to another column name. It is likewise used to change the name of a table to another table name. Let us see how to utilize these keywords in various databases. Let us consider the below table to understand all the examples:

Renaming Column Name in SQL

The syntax to rename a column name in MySQL, MariaDB, Oracle, and PostgreSQL is the same.

1. Renaming a column name using the ALTER keyword.

Syntax:

Line 1: ALTER TABLE TableName

Line 2: RENAME COLUMN OldColumnName TO NewColumnName;

•         For Example: Write a query to rename the column name “SID” to “StudentsID”.

Line 1: ALTER TABLE Students;

Line 2: RENAME COLUMN SID TO StudentsID;

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

Output:

StudentsID	SName	Marks
1	Harry	70
2	Rick	50
3	Martha	80
4	Gwen	60
5	John	75

 2. Renaming a column name using the CHANGE keyword.

Syntax

Line 1: ALTER TABLE TableName

Line 2: CHANGE COLUMN OldColumnName NewColumnName Data Type;

For Example: Write a query to rename the column name “SID” to “StudentsID”.

Line 1: ALTER TABLE Students;

Line 2: CHANGE COLUMN SID StudentsID INT;

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

Must Read: SQL Project Idea

The Rename Column Name in MS SQL Server

The technique to rename a section name in MS SQL Server is distinctive when contrasted with different databases. In MS SQL Server, you should utilize the stored procedure methodology called sp_rename.

The technique to rename a section name in MS SQL Server is distinctive when contrasted with different databases. In MS SQL Server, you should utilize the stored procedure methodology called sp_rename. The standard stored procedure known as sp_rename modifies a user-created object’s name in the existing database. If you know how to rename column name in SQL server, also note that the user-created object can be a column, table, alias data type, index, etc.

Stored procedures and scripts may be broken when we alter a certain part of an object’s name. It is recommended to drop the object and reform it using the new name instead of using this statement to alter the name of user-defined functions, triggers, views, stored procedures, etc.

Syntax:

Line 1: sp_rename ‘TableName.OldColumnName’, ‘New ColumnName’, ‘COLUMN’;

For Example:

Write a query to rename the column name “SID” to “StudentsID”.

Line 1:  sp_rename ‘Students.SID’, ‘StudentsID’, ‘COLUMN’;

Output:

StudentsID	SName	Marks
1	Harry	70
2	Rick	50
3	Martha	80
4	Gwen	60
5	John	75

Renaming Multiple Columns in SQL 

With MYSQL, you can rename column name in SQL with a single command. This can be done with the same RENAME TABLE and the CHANGE command. 

Let us see the syntax for both: 

Syntax for RENAME TABLE: 

Line 1: ALTER TABLE table_name

Line 2: RENAME COLUMN old_column_name1 to new_column_name1,

Line 3: RENAME COLUMN old_column_name2 to new_column_name2,

Line 4: RENAME COLUMN old_column_name3 to new_column_name3;

Syntax for CHANGE: 

 

Line1: ALTER TABLE table_name

Line 2: CHANGE old_column_name1 to new_column_name1,

Line 3: CHANGE old_column_name2 to new_column_name2,

Line 4: CHANGE old_column_name3 to new_column_name3;

We will see an example of the same later in the blog.

Now, that you know how to rename a column name in various databases, let us also understand how renaming a table name works. You can understand the practical implementation of how to rename column name in SQL server in the below ways:

What is the SELECT Command?

The SELECT command in SQL is used for selecting data from a pre-existing table. Let us see the syntax and an example to understand. 

Syntax: 

Line 1: SELECT column_name FROM table_name;

If you want to return all columns on a table, without having to mention every column’s name, you can use the SELECT* command. 

Rename a table name
 To rename a table name in MySQL, MariaDB, Oracle databases, the RENAME command can used in SQL as:

Syntax:

Line 1: ALTER TABLE OldTableName

Line 2: RENAME TO NewTableName;

For Example:

Line 1: ALTER TABLE Students

Line 2: RENAME TO ListOfStudents;

On executing the below SELECT query, you will get the complete details of the table ListOfStudents.

Query:

Line 1: SELECT * FROM ListOfStudents;

Output:

StudentsID	SName	Marks
1	Harry	70
2	Rick	50
3	Martha	80
4	Gwen	60
5	John	75

Also Read: SQL Interview Questions & Answers

Here are several other methods to rename column name in SQL server:

 Rename a column using Object Explorer

1. Rename a column using Table Designer

iii. Rename a column using Double click on the column name

 Let’s understand each of them:

 Rename a column using Object Explorer:

Here are the steps to rename column name in SQL server using Object Explorer:

 Step-1: Open the ‘Tables’ menu from ‘Databases’ and expand it.

Step-2: Choose the specific table and expand it.

Step-3: Choose the columns and expand them.

Step-4: Now, right-click on the column name you want to modify and choose the ‘Rename’ option.

Step-5: Give a new name to your selected column.

Step-6: Refresh the table or database to complete the renaming steps.

Step-7: Implement the SELECT statement to confirm the changed column name.

 Steps to rename a column using Table Designer

Here are the steps explaining how to change column name in SQL using Table Designer:

 Step-1: Open the ‘Tables’ menu from ‘Databases’ and expand it.

Step-2: Choose the specific table wherein you wish to modify the column name. Now you need to open the ‘Context’ menu and choose the ‘Design’ option. You will see the table in design mode.

Step-3: Choose columns whose names you want to change. Give them a new name.

Step-4: Click the ‘Close’ button and then the ‘Yes’ button in the pop-up window to save the changes you did in the design window.

Note: You can also save the changes done in the design window. Open the ‘File’ menu, press CTRL+S on the keyboard, or click on the ‘Save table name’.

Step-5: Implement the SELECT statement to confirm the changed column name.

 iii. Steps to rename a column using Double click on the column name:

Here are the steps explaining how to change column name in SQL by Double click on the column name:

 Step-1: Follow this path: Databases > Tables > Columns.

Step-2: Choose the column name you want to change and then double-click.

Step-3: Give a name to your selected column.

Step-4: Refresh the table or database to complete the renaming steps.

Step-5: Implement the SELECT statement to confirm the changed column name.

blogs

Exceptions/Errors when renaming a column in SQL 

If you are trying to update column name in SQL, certain errors or exceptions could arise while renaming a column. Let us take a look at some of them: 

• Duplicate name: When changing column name in SQL you need to make sure that there isn’t a column with the same name. If there is, SQL will show an exception. 
• Does not exist: If you rename column name in SQL which does not exist in the table, then SQL will show a does not exist exception. 
• Index constraints: If the column you are trying to rename is a part of an index, then there is going to be some problem in the operation. To surpass this issue, you need to drop the index, change column name, and recreate the index. 
• Permission exceptions: SQL will show an exception if you try to rename a column without having modification permission on the table/database. 
• Foreign key constraints: If the column you are trying to rename has some foreign key constraints, then the key needs to be first dropped and only then you can perform column name change in SQL. 

Other Actions With ALTER TABLE

Here are some other commands that you can execute with the ALTER TABLE command. 

Adding a column in SQL 

To add columns to an existing table, you can use the ALTER TABLE command, the name of the table, and the datatype. The syntax for adding a column is as follows: 

Syntax: 

Line 1: ALTER TABLE table_name

Line 2: ADD column_name datatype; 

For example: 

Line 1: ALTER TABLE ListOfStudents 

Line 2: ADD Age INT; 

Line 3: SELECT*FROM ListOfStudents;

On executing this query, the output is: 

Table name: ListOfStudents 

SID	SName	Marks	Age
1	Harry	70
2	Rick	50
3	Martha	80
4	Gwen	60
5	John	75

Adding multiple columns in SQL

Using the same syntax listed above, you can add multiple columns to your existing table: 

Syntax: 

Line 1: ALTER TABLE table_name

Line 2: ADD column_1_name datatype

Line 3: ADD column_2_name datatype;

Query: 

Line 1: ALTER TABLE ListOfStudents 

Line 2: ADD Age INT; 

Line 3: ADD PhoneNumber VARCHAR (20), 

Line 4: ADD Gender VARCHAR (1),

Line 5: SELECT*FROM ListOfStudents;

Output: 

Table name: ListOfStudents

SID	SName	Marks	Age	PhoneNumber	Gender
1	Harry	70
2	Rick	50
3	Martha	80
4	Gwen	60
5	John	75

Deleting columns in SQL 

Now that you know how to add a column in SQL, you should also know how to delete any particular column. This is done with the help of the ALTER TABLE command and the DROP command. Let us understand the syntax: 

Syntax: 

Line 1: ALTER TABLE table_name; 

Line 2: DROP column_name; 

Query: 

Line 1: ALTER TABLE ListOfStudents

Line 2: DROP COLUMN PhoneNumber

Line 3: SELECT*FROM ListOfStudents;

On executing this query, the output is as follows: 

Output: 

Table name: ListOfStudents

SID	SName	Marks	Age	Gender
1	Harry	70
2	Rick	50
3	Martha	80
4	Gwen	60
5	John	75

Alter the datatype in SQL 

Apart from changing column name in SQL, you can also change the pre-existing datatype of a column in a table. Let us see the syntax to understand better: 

Syntax: 

For MS Access/SQL Server: 

Line 1: ALTER TABLE table_name 

Line 2: ALTER COLUMN name_of_column datatype; 

For Oracle (before 10G version)/My SQL: 

Line 1: ALTER TABLE table_name 

Line 2: MODIFY COLUMN name_of_column datatype; 

For Oracle 10G and next versions:

Line 1: ALTER TABLE table_name 

Line 2: MODIFY name_of_column datatype; 

Change Column Name in PostgreSQL 

Just like you change name of column in SQL, in PostgreSQL, you need to do the same thing. For understanding, let us take another table for example: 

Table name: Employees 

Employee_ID	Name	Age	Gender	Phone_Number
1	John	35	Male	1111111111
2	Luke	27	Male	2222222222
3	Sarah	31	Female	3333333333
4	Jennifer	25	Female	4444444444
5	Joe	39	Male	5555555555
6	Caroline	32	Female	6666666666
7	Teddy	29	Male	7777777777

Syntax: 

Line 1: ALTER TABLE table_name 

Line 2: RENAME COLUMN old_column_name to new_column_name 

Query: 

Line 1: ALTER TABLE Employees 

Line 2: RENAME COLUMN Name TO Employee_Name 

Line 3: SELECT*FROM Employee;

On executing this query, the output is: 

Table name: Employees 

Employee_ID	Employee_Name	Age	Gender	Phone_Number
1	John	35	Male	1111111111
2	Luke	27	Male	2222222222
3	Sarah	31	Female	3333333333
4	Jennifer	25	Female	4444444444
5	Joe	39	Male	5555555555
6	Caroline	32	Female	6666666666
7	Teddy	29	Male	7777777777

Conclusion

With that, we conclude this article on the renaming of a column name in SQL. You will find these methods much more useful and simpler after more hands-on practice. If you wish to study MySQL, MariaDB, Oracle, and PostgreSQL become more acquainted with this open-source social information base, take a look at upGrad courses that accompany genuine task insight. It will assist you with learning inside and out and help you master the subject.

All in all, learning and getting hands-on practice on all the databases mentioned in this article will add to your experience. Go through the course overview, learning, and job opportunities involved with it—platforms like upGrad offer power-packed courses designed by the highly reputed institutes like IIIT-B in Full Stack Development.

You get to be a part of an extensive network and meet a lot of people working in the same domain. Also, you are mentored by industry experts who resolve your doubts and support you every time. And one of the unique things you get is that at upGrad, you can opt for a No Cost EMI option and go easy on your pockets.