SQL RENAME TABLE Command
We can rename any table by using RENAME sql command. The data will not be lost. Only the table name will be changed to new name. Here is the command to change the name of a table. Here we will change the name of our student table name to student_new table.
We can also copy data from one table to a new table. Same way we can copy data from one table with WHERE clause to an existing table.
This sql command will rename the student table to student_new
Sample PHP Script to change name of the table.We will use PDO functions to manage MySQL database from PHP. We will establish connection to MySQL database first by including the config.php file. Read more on database connection using PDO here.
Below code will print success or failure message based on the status of the query execution.
After changing the name of the table we can check by displaying all the name of the tables present in the database. Here is the code for that. You can add below code at the end of the table name change code given above.
Renaming all the tables of a databaseWe can add ( prefix ) a text string to all the tables present in a database or we can remove a fixed length of characters from all the tables names.
We will be using PHP script with PDO support for this.
While listing all the tables by using show tables query inside while loop we will prepare two variables.
Adding string before the nameThe first variable will store the present name of the table by taking the data from the query. The second variable we will prepare by prefixing a string to the names. Here we have added plus2net_ before the name of the table.
Removing string from the name of the table. We have added the string plus2net_ to all the tables, now to remove the strings we will change the variable like this.
Note that the length of the string we want to remove plus2net_ is of 9 char length. You can read more on substr string function here.
Replacing the string with new string from the name of the table.We can replace part of the table name with another string by using str_replace() string function. Here is how the variables is changed.
The full script is here, based on requirements you can use the variable, lines commented inside the script
In the above script database connection is kept inside config.php file.
Note that the database you selected inside config.php file should be same as you select in show table query
This article is written by plus2net.com team.
More on Alter or coy table , add update or delete records in SQL