How to Rename a Table in SQL
There are many reasons which involve a small or big change in a database name after creating it. That’s why many people might look for a way to rename their SQL database table.
These are different methods which could be recommended for the task without requiring to dropping the table and creating it from scratch.
ALTER TABLE statement
The main way to change a table name in SQL would be using ALTER TABLE syntax.
Just note that only some databases (MariaDB, MySQL and Oracle) support the command, whereas others (such as SQL Server) don’t.
The syntax could be like this:
ALTER TABLE old_name
RENAME TO new_name;
Another good thing about this syntax is that you could also use it to set new names for the table columns as well.
Look at these examples:
ALTER TABLE name_of_table
CHANGE COLUMN oldname TO newname;
There is a slight difference when using the above command to rename column names. While this query could be used in MariaDB and MySQL databases, you have a change it a little for Oracle database.
The change is as simple as using ‘RENAME’ instead of ‘CHANGE’ in the query.
Change table names in phpMyAdmin
Many users might prefer the easier method of using phpMyAdmin database manager to run different SQL queries including renaming database tables.
Finding the tool is very simple on most PHP-powered hosts especially those managed by cPanel.
Please follow these simple steps to do the task:
- Click on phpMyAdmin on your web host panel.
- Usually it logins automatically, unless you have to enter login credentials (username, password) manually.
- Once in the database tool, select the database from the list, which includes the table which should be renamed.
- At the top, find the SQL tab and click on it.
- Type this command in the text box:
ALTER TABLE oldtable RENAME TO newtable;
- Put the actual table name you want to change instead of ‘oldtable’ above.
- Likewise, ‘newtable’ should be replaced with whatever you want as its new name.
- At the end, click on this button: ‘Go’.
Using SSMS to rename a database table
SQL Server Management Studio (or SSMS in short) provides functions which make it very easy to change the name of a table.
The method is as simple as the following steps:
- ‘Rename’ menu item should be selected after finding the table name in the list and then right-clicking on it.
- Edit the table name as you want.
- Press the Enter at the end.
That’s it. After these steps your SQL table could be used by its new name.
Table rename with Transact SQL
In order to directly rename a table, there is no statement in SQL server to use. But, a table name change is possible using ‘sp_rename’ which is a stored procedure.
Look at the below example to see how this syntax works:
EXEC sp_rename ‘old_t_name’, ‘new_t_name’
Please remember that you should use single quotations to enclose the new and old names of the table.
And after running the sp_rename syntax, a caution message could be returned by SQL Server about the risk of breaking stored procedures and scripts as a result.
Nevertheless, the rename could be executed successfully.