How to Rename Column Name in SQL?

What is SQL?

SQL, or Structured Query Language, is a programming language used to manage and access data in relational databases. You can use SQL Commands to retrieve, update, and control data in the database.

Why Rename a Column in SQL?

You should rename a column when its current name no longer fits its data. For example, if a column is named “ID” but holds student IDs, changing it to “StudentID” makes the data clearer. By renaming columns, you can:

• Improve readability: Clear names make your database easier to understand.
• Maintain consistency: Keep naming conventions uniform across your tables.
• Reflect data changes: Update names when the data they hold changes purpose.

How to Rename a Column Name in SQL

The process to rename a column varies depending on the database system you use.

Let’s use an example table named Students to demonstrate. This table has the following columns:

You want to rename the column SID to StudentsID.

Rename Column in MySQL, MariaDB, Oracle, and PostgreSQL

These database systems use a similar command structure to rename columns. You use the ALTER TABLE command with RENAME COLUMN.

Syntax:

ALTER TABLE TableName
RENAME COLUMN OldColumnName TO NewColumnName;

Steps:

• Identify the table: Know the name of the table containing the column you want to rename (e.g., Students).
• Identify the old column name: Get the current name of the column (e.g., SID).
• Choose the new column name: Decide on the new name for the column (e.g., StudentsID).
• Execute the query: Run the ALTER TABLE command.

Example:

To rename the SID column to StudentsID in the Students table:

ALTER TABLE Students
RENAME COLUMN SID TO StudentsID;

After you run this query, the Students table will look like this:

Rename Column in MS SQL Server

The process of renaming column name in 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', 'NewColumnName', 'COLUMN';

Steps:

• Specify the current column path: Combine the table name and the old column name with a dot (e.g., ‘Students.SID’).
• Provide the new column name: Enter the desired new name (e.g., ‘StudentsID’).
• Indicate the object type: Use ‘COLUMN’ to specify that you are renaming a column.
• Execute the stored procedure: Run the sp_rename command.

Example:

To rename the SID column to StudentsID in the Students table:

EXEC sp_rename 'Students.SID', 'StudentsID', 'COLUMN';

The EXEC keyword is used to execute stored procedures, but it is optional in many cases when calling sp_rename.

Running this command will give you the same result as the previous example table.

Conclusion

Renaming columns in SQL is a simple process once you know the correct commands for your database system. Using clear column names improves database readability and maintainability, helping you manage your data more effectively.

Also Read:

• What are SQL Queries? 40+ SQL Queries to Practice
• SQL Joins and How to Use Them