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.
In MySQL, we can rename a table using one of the following methods:
RENAME OLD_TABLE_NAME TO NEW_TABLE_NAME
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;
In Oracle, we can rename a table using the same two ways we listed above for MySQL.
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’;