SQL RENAME TABLE

---

---

The SQL RENAME TABLE Command

The RENAME TABLE command is used in SQL to change the name of an existing table. This is useful when you want to give your table a new name without affecting the data or structure of the table.

For example, if your table is named CUSTOMERS and you want to rename it to BUYERS, you can use the RENAME command to make this change easily.

However, not all database systems use the same syntax for renaming a table. The command differs between MySQL, SQL Server, and PostgreSQL:

• MySQL directly supports RENAME TABLE.
• SQL Server does not support this directly. You must use a special command called sp_rename.
• PostgreSQL does not support RENAME TABLE, but allows renaming using ALTER TABLE ... RENAME TO command.

Renaming a Table in MySQL Server

In MySQL, the RENAME TABLE command is used to rename a table. It is a simple and direct method to change a table name.

Syntax

Following is the basic syntax to rename a table in MySQL Server:

RENAME TABLE old_table_name TO new_table_name;

Example

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:

In the following example query, we are renaming the table CUSTOMERS to BUYERS in MySQL Server:

RENAME TABLE CUSTOMERS TO BUYERS;

We get the output as shown below:

Query OK, 0 rows affected (0.02 sec)

This command only changes the table name. All existing data and column definitions remain unchanged:

Verifying the Rename

After renaming a table, you can confirm the change by using the following SQL command:

SHOW TABLES;

This command will list all the tables in the current database. You should now see BUYERS instead of CUSTOMERS:

You can also verify the renamed table by retrieving its data using the SELECT statement as shown below:

SELECT * FROM BUYERS;

This will display all records from the newly renamed BUYERS table:

Renaming a Table in SQL Server

SQL Server does not support RENAME TABLE command directly. Instead, it uses a built-in stored procedure called sp_rename to rename tables.

Syntax

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

EXEC sp_rename 'old_table_name', 'new_table_name';

Example

The following command renames the above created CUSTOMERS table to WORKERS in SQL Server:

EXEC sp_rename 'CUSTOMERS', 'WORKERS';

We get the output as shown below:

Caution: Changing any part of an object name could break scripts and stored procedures.
Completion time: 2025-07-29T15:46:44.1359549+05:30

SQL Server shows this standard cautionary message after a successful rename.

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:

Since you have renamed the table to WORKERS, if you try to retrieve the details using the old table name, it will throw an error indicating that the table does not exist.

Renaming a Table in PostgreSQL

In PostgreSQL, the renaming is done using the ALTER TABLE command with a RENAME TO clause.

Syntax

Following is the basic syntax to rename a table in PostgreSQL:

ALTER TABLE old_table_name RENAME TO new_table_name;

Example

In this example query, we rename the above created CUSTOMERS table to BUYERS in PostgreSQL:

ALTER TABLE CUSTOMERS RENAME TO BUYERS;

We get the output as shown below:

ALTER TABLE

PostgreSQL outputs the keyword ALTER TABLE to confirm that the command executed successfully.

Verification

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

SELECT * FROM BUYERS;

This will list down all the records available in BUYERS table as follows:

Important Points on Renaming Table

Following are a few important points to remember before renaming a table in a database:

• The RENAME command only changes the name of the table. It does not change the data or structure.
• Make sure no other part of your application depends on the old table name.
• You must have the required permission to rename a table.
• Each database system has its own preferred syntax. Use the correct one to avoid errors.