Bulk Rename - Best Practices?

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

Re: Bulk Rename - Best Practices?

Carl Pearson wrote:

> 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


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...


--
Michael Austin
Database Consultant
Domain Registration and Linux/Windows Web Hosting Reseller
http://www.spacelots.com