Bulk Rename - Best Practices?

[Carl Pearson]

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.

Finally:

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?

Thanks!
--
Carl

[Michael Austin]

Post by Carl Pearson
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?
Thanks!
--
Carl

This will create output that can be executed.


mysql> select 'rename table '
||table_schema||'.'||table_name
||' 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...