Rename Table in SQL

Sometimes, it may be necessary to rename a table. There is no standard way to rename a table, and the implementation varies by RDBMS. Below we discuss how we can rename a table in MySQL, Oracle, and SQL Server.

MySQL

In MySQL, we can rename a table using one of the following methods:

Method 1

RENAME OLD_TABLE_NAME TO NEW_TABLE_NAME

Method 2

ALTER TABLE OLD_TABLE_NAME
RENAME TO NEW_TABLE_NAME

For example, to change the name of a table from Top_Scores to Best_Scores, we can use either of the following:

RENAME Top_Scores TO Best_Scores;

ALTER TABLE Top_Scores
RENAME TO Best_Scores;

Oracle

In Oracle, we can rename a table using the same two ways we listed above for MySQL.

SQL Server

In SQL Server, one cannot use SQL to rename a table. Instead, it is necessary to use the sp_rename stored procedure to do so. The syntax is:

sp_rename ‘OLD_TABLE_NAME’, ‘NEW_TABLE_NAME’

Note the single quote above. Using a double quote or no quote will both result in an error. To rename a table from Top_Scores to Best_Scores, we use the following:

sp_rename ‘Top_Scores’, ‘Best_Scores’;

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>