MySQL: Batch-Renaming of Tables

Recently I had the following problem: I had a database (let’s call it “exampledb”) whose tablenames all had a constant prefix like “DUMMYTEXT_”. To get rid of this prefixes I had to rename every table via

"ALTER TABLE DUMMYTEXT_TAB1 RENAME TO TAB1"

There were ~100 tables and this would have been a very annoying task doing it manually for every table.
Luckily there is a way to do the renaming in a batch for multiple tables, which makes it a matter of a few minutes.

Every MySQL-Server hosts a schema that contains the metadata of all databases on that server – the INFORMATION_SCHEMA. In that schema is a table called “TABLES” which gives us the information we need: all tablenames of my to-be-renamed exampledb-tables. So I created the appropriate sql-statement, piped it on a bash-shell into the “mysql”-executable and stored the result in a file called “rename.sql”:

echo "select concat('alter table ',table_name,' rename to ', substr(table_name,11),';') from information_schema.TABLES where table_schema='exampledb';" | mysql -u root -pPWD exampledb > ./rename.sql

In the above command the “SUBSTR(table_name,11)” cuts off the first 10 letters (“DUMMYTEXT_”) of every tablename. The CONCAT builds all necessary rename-statements with the remaining string. The “mysql -u root -pPWD exampledb” logs via mysql-executable into DB “exampledb” with user “root” using password “PWD” (mind the omitted whitespace behind parameter “-p”). The output of that command is then written to the file “./rename.sql”.

The first line in the resulting file “rename.sql” contains the above sql-statement.
To remove the sql-command from the rename-file one could use “sed”:

sed -i '/^select/d' ./rename.sql

After removing that line I had a rename-command-file that I now could feed into my database to correct my tablenames:

mysql -u root -pPWD exampledb < ./rename.sql

edit 2013-04-02
Here is a smarter approach as a one-liner:

E:\APPS\xampp\mysql\bin>mysql -u root -e "show tables;" test
+----------------+
| Tables_in_test |
+----------------+
| dummytext_tab1 |
| dummytext_tab2 |
| dummytext_tab3 |
| dummytext_tab4 |
| dummytext_tab5 |
+----------------+

E:\APPS\xampp\mysql\bin>mysql -u root -e "select concat('alter table ',table_name,' rename to ',
substr(table_name,11),';') from information_schema.tables where table_schema='test';" --column-names
=false test | mysql -u root test

E:\APPS\xampp\mysql\bin>mysql -u root -e "show tables;" test
+----------------+
| Tables_in_test |
+----------------+
| tab1           |
| tab2           |
| tab3           |
| tab4           |
| tab5           |
+----------------+

E:\APPS\xampp\mysql\bin>

With this command I can do all the above without resorting to “echo” and “sed” in one line and without using an auxiliary command-file:

mysql -u root -e "select concat('alter table ',table_name,' rename to ', substr(table_name,11),';') from information_schema.tables where table_schema='test';" --column-names=false test | mysql -u root test

Instead of “echo” the “-e” executes the given SQL and in place of “sed” I suppress the printing of the column-label with “–column-names=false”. So I can pipe the resulting Rename-SQL directly back to mysql and do the renaming.
Please notice that in this example the name of my database is “test” – not “exampledb” as above and that I omitted the “-p” switch as my root-user on this test-instance runs without password.