SQL - Rename Table

---

---

SQL provides two ways to rename an MySQL table. You can use either SQL RENAME TABLE or ALTER TABLE statement to change a table name in MySQL RDBMS.

The SQL RENAME TABLE Statement

You can change a MySQL table name using SQL RENAME TABLE statement.

Syntax

Following is the syntax of the SQL RENAME TABLE Statement −

RENAME TABLE table_name TO new_table_name;

Where, table_name is the current name of an existing table and new_table_name is the new name of the table.

Example: SQL RENAME TABLE Statement

Let us create a table with the name CUSTOMERS which contains the personal details of customers including their name, age, address and salary etc. as shown below −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Now, let us insert few records into this table using the INSERT statement as follows −

INSERT INTO CUSTOMERS VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

The table will be created as follows −

Following SQL Query changes the name of the CUSTOMERS table to BUYERS −

RENAME TABLE CUSTOMERS to BUYERS;

Verification

Once you change the name of a table, you can start using the new table name in your SQL queries.

SELECT * FROM BUYERS;

If table name got changed successfully, then it should list down all the records which were available in CUSTOMERS table.

The SQL ALTER TABLE Statement

The ALTER TABLE statement can be used to change or modify the structure of an existing table i.e. using this statement you can add/delete columns, create/destroy indexes, change the datatypes of the existing columns, rename the columns and, we can even rename the table.

Syntax

Following is the syntax of the SQL ALTER TABLE statement to rename an existing table −

ALTER TABLE table_name RENAME [TO|AS] new_table_name

Example: SQL ALTER TABLE Statement

Following SQL ALTER TABLE statement will change the table name from BUYERS to CUSTOMERS.

ALTER TABLE BUYERS RENAME TO CUSTOMERS;

Verification

Once you change the name of the table to CUSTOMERS, you can start using this name in your SQL queries.

SELECT * FROM CUSTOMERS;

This will produce the following result:

Renaming a Table in SQL Server

There isn't a query in SQL Server that can rename a table directly. However, it does give you access to a stored procedure called sp_rename that enables you to rename a table.

The sp_rename is a system stored procedure (set of pre-built subroutines that perform tasks within the database) in SQL that can be used to rename various database objects including tables, columns, indexes, and constraints.

Syntax

Following is the basic syntax to rename a table in SQL Server −

EXEC sp_rename 'old_table_name', 'new_table_name'

Here, you must ensure that old table name is present in the database and that new table name does not already exist. Otherwise, it will issue a warning. Second important point is to make sure that the table is not locked and there is no active transaction involving this table.

Example: Renaming a Table in SQL Server

Assume we already have the CUSTOMERS table in our database. Now, we are going to rename this table from CUSTOMERS to WORKERS, using the following query −

EXEC sp_rename 'CUSTOMERS', 'WORKERS';

Output

The result obtained is as shown below −

Completion time: 2023-08-15T19:21:49.1144163+05:30

Verification

We can verify whether the changes are reflected by retrieving its contents using the SELECT statement as follows −

SELECT * FROM WORKERS;

This will list down all the records available in WORKERS table as follows −

Because you have renamed the table to WORKERS so if you try to get the details by using the old table name, it will throw an error showing that the table does not exist.

Rules to be followed while renaming tables

When renaming tables in SQL, there are some rules and best practices that should be followed to ensure that the renaming process goes smoothly and does not cause any unintended consequences or issues.

• Avoid renaming system tables − System tables are tables that are created and used by the database management system itself. Renaming these tables can cause issues with the functioning of the database system, so it is generally not recommended to rename system tables.

• Update all references to the table − After renaming a table, any stored procedures, views, triggers, or other database objects that reference the table will need to be updated to use the new name of the table. Failure to update these references can result in errors or issues with the functioning of the database system.

• Test thoroughly − Before renaming a table in a production environment, it is important to test the renaming process thoroughly in a development or testing environment to ensure that all references to the table have been updated correctly and that the database system continues to function as expected.

• Use a consistent naming convention − It is a good practice to use a consistent naming convention for tables and other database objects to make it easier to understand and maintain the database system. If you need to rename a table, consider following the same naming convention that you have used for other tables in the database.

• Backup the database − Before renaming a table, it is recommended to create a backup of the database to ensure that you have a restore point; in case anything goes wrong during the renaming process.