SQL Rename Table: How To with Examples

Once you create a table in SQL, you may want to rename it. This might happen if you are creating a new table to replace it, or have come up with a better name for it.

In this article, you’ll learn the SQL commands to rename a table in Oracle, SQL Server, MySQL, and PostgreSQL, along with examples of each.

Let’s get into the article.

Summary

Here are the commands to rename a table in each of these databases:

EXEC sp_rename 'schema.old_name' 'new_name';

ALTER TABLE old_name RENAME TO new_name;

ALTER TABLE old_name RENAME TO new_name;

ALTER TABLE old_name RENAME TO new_name;

There is no specific SQL rename table command, but you can use either sp_rename or the ALTER TABLE command.

Let’s take a look at them in more detail and see some examples.

SQL Server Rename Table

To rename a table in SQL Server you use the sp_rename procedure. The procedure can be called like this:

EXEC sp_rename 'schema.old_name' 'new_name';

You specify the schema that the table exists in, and the old or current table name, inside quotes. You then specify the new table name.

Note: don’t add the schema to the new table name. SQL Server does this automatically, and if you add the schema name, you’ll end up with a duplication in your name (e.g. dbo.dbo.sales instead of dbo.sales).

Here’s an example. To rename the “employee” table to “person” in the dbo schema:

EXEC sp_rename 'dbo.employee' 'person';

The table will then be updated.

A query like this will select the data from the updated table:

SELECT *
FROM person;

Oracle SQL Rename Table

To rename a table in Oracle SQL, use the ALTER TABLE statement, in the same way as MySQL and PostgreSQL:

ALTER TABLE old_name
RENAME TO new_name;

You simply add in your current table name and the new table name and run the command. There’s no need to specify the schema name.

For example to rename the “employee” table to “person”:

ALTER TABLE employee
RENAME TO person;

This will rename the employee table to person.

You can run a simple SELECT query on the new table to see the data.

ALTER TABLE employee
RENAME TO person;

MySQL Rename Table

Renaming a table in MySQL is done using the ALTER TABLE statement, similar to PostgreSQL and Oracle:

ALTER TABLE old_name
RENAME TO new_name;

You add in the old name or the current name of the table, and the new name of the table.

For example, to rename the “employee” table to “person”, you can run this command:

ALTER TABLE employee
RENAME TO person;

This will rename the employee table to person.

PostgreSQL Rename Table

To rename a table in PostgreSQL, run the ALTER TABLE statement (in the same way as MySQL and Oracle):

ALTER TABLE old_name
RENAME TO new_name;

You substitute in the old name or the current name of the table, and the new name of the table, then run the command.

For example, to rename the “employee” table to “person”, you can run this command:

ALTER TABLE employee
RENAME TO person;

This will rename the employee table to person.

After Renaming Your Table

Once you have run the command to rename your table, it’s a good idea to update any references to that table in your database. If you don’t, then these references won’t be valid anymore and things may break.

This means you should:

• Check any views that refer to the table and update the view to refer to the new name.
• Check any stored procedures or functions that refer to the old table name, and update them to refer to the new name
• Check any other objects you may be using, such as synonyms, and update those.
• Check any scripts that are not stored in your database that may refer to the table, and update those.

Views and stored procedures are the most common from what I can see.

Conclusion

Renaming a table in SQL is fairly easy. You can do it in Oracle, MySQL, and PostgreSQL using the ALTER TABLE command, and SQL Server has the sp_rename procedure instead.