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’;

Tags: Tags: ,