SQL Rename Table

Introduction

In this chapter, we will learn how to rename a table in SQL using the RENAME TABLE statement. Renaming a table can be necessary when you want to give the table a more meaningful name or if you are reorganizing your database schema. This chapter will guide you through the syntax and provide examples to help you understand how to rename tables effectively.

Syntax for Renaming a Table

The RENAME TABLE statement is used to rename an existing table in a database. This operation changes the name of the table but retains all its data, structure, and indexes.

Basic Syntax

RENAME TABLE old_table_name TO new_table_name;

• old_table_name: The current name of the table you want to rename.
• new_table_name: The new name you want to assign to the table.

Example

Let's assume we have a table named employees that we want to rename to staff.

RENAME TABLE employees TO staff;

Viewing the Table Structure

You can view the structure of a table using the DESCRIBE statement. This is useful to verify the structure before and after renaming the table.

Syntax

DESCRIBE table_name;

Example

Before renaming:

DESCRIBE employees;

After renaming:

DESCRIBE staff;

Output Example

Before Renaming

Field	Type	Null	Key	Default	Extra
id	int(11)	NO	PRI	NULL	auto_increment
first_name	varchar(50)	YES		NULL
last_name	varchar(50)	YES		NULL
email	varchar(100)	YES	UNI	NULL

After Renaming

Field	Type	Null	Key	Default	Extra
id	int(11)	NO	PRI	NULL	auto_increment
first_name	varchar(50)	YES		NULL
last_name	varchar(50)	YES		NULL
email	varchar(100)	YES	UNI	NULL

Using ALTER TABLE to Rename a Table (MySQL and MariaDB)

In some databases like MySQL and MariaDB, you can also use the ALTER TABLE statement to rename a table.

Syntax

ALTER TABLE old_table_name RENAME TO new_table_name;

Example

ALTER TABLE employees RENAME TO staff;

Conclusion

Renaming a table is a straightforward process in SQL. Using the RENAME TABLE or ALTER TABLE statements, you can easily change the name of a table while retaining its data and structure. This operation is useful when you need to update table names to better reflect their contents or purpose.