Bulk Rename - Best Practices?

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
Howdy, All,

Was trying to modify all tables in a particular database starting with
the same prefix, i.e., "tbl_".

Decided I'd rather have them all start with "_tbl_" instead.

Being lazy, didn't want to rename them one at a time.


1) Went into phpMyAdmin's SQL window.

2) Ran "show tables like 'tbl_%';"

3) Copied the resulting column into my text editor.

4) Fiddled with regex search / replace & a tiny macro to create
    "rename table tbl_mytable to _tbl_mytable"
    and so on, one table per line.

5) Ran that file as source in a CLI session.

So, they're renamed.  But am just curious if there was a more direct
way, rather than having to suck all the names in & massage a source file.

AFAIK, RENAME does not support wildcards, which is where I got stuck.

So, the question is: Was there a way to have done this entirely within
MySQL (i.e., with one statement), as opposed to the method I used?


Re: Bulk Rename - Best Practices?

Carl Pearson wrote:

Quoted text here. Click to load it

This will create output that can be executed.

mysql> select 'rename table '
    ||' to '||table_schema||'._'||table_name';'
    as runthis
    from  information_schema.tables
    where  table_name like 'tbl%';
| runthis                                         |
| rename table mysql.tbl_test to mysql._tbl_test; |

the double pipe is the ANSI standard for concatenate.  I suppose you could write
this with CONCAT(), but I prefer this way...

of course you can then take the output - remove the "|" and paste it back into
your session. I am sure there are other ways, this is just off the top...

Michael Austin
Database Consultant
Domain Registration and Linux/Windows Web Hosting Reseller

Site Timeline