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.

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

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. 

Explore our Popular Software Engineering Courses

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.

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:

 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 Ideas

Explore Our Software Development Free Courses

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:

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:

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.

Read our Popular Articles related to Software Development

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.