SQL command to remove prefix from a set of tables

Question

I am no DBA. I have a situation with a MySQL database (5.0.51a) where I want to remove a 'dr_' prefix to all tables having this prefix (about 110 tables). I could rename them one by one of course, but I was wondering whether there was an SQL command to perform this in one shoot?

To make it clear, a table called dr_hjkd would have to be renamed hjkd. A table called rfefd would keep the same name. Thanks.

Answer 1

Wow I answered a similar question a year ago.

Nevertheless, your question is unique.

Here it goes:

If you are renaming all tables in the database mydb, here is the query you need:

SELECT 
CONCAT('ALTER TABLE ',db,'.',old_tblname,' RENAME ',db,'.',new_tblname,';')
FROM
(
    SELECT
        table_schema db,
        table_name old_tblname,
        substr(table_name,4) new_tblname
    FROM
        information_schema.tables
    WHERE
        SUBSTR(table_name,1,3)='dr_'
        AND table_schema = 'mydb'
) A;

Run this in the OS and capture it to a SQL file. Then, execute the SQL file.

mysql -u... -p... -AN -e"SELECT CONCAT('ALTER TABLE ',db,'.',old_tblname,' RENAME ',db,'.',new_tblname,';') FROM (SELECT table_schema db,table_name old_tblname,substr(table_name,4) FROM information_schema.tables WHERE SUBSTR(table_name,1,3)='dr_' AND table_schema = 'mydb') A" > BigRename.sql
mysql -u... -p... < BigRename.sql

If you are renaming all tables in all databases, here is the query you need:

SELECT 
CONCAT('ALTER TABLE ',db,'.',old_tblname,' RENAME ',db,'.',new_tblname,';')
FROM
(
    SELECT
        table_schema db,
        table_name old_tblname,
        substr(table_name,4) new_tblname
    FROM
        information_schema.tables
    WHERE
        SUBSTR(table_name,1,3)='dr_'
) A;

Run this in the OS and capture it to a SQL file. Then, execute the SQL file.

mysql -u... -p... -AN -e"SELECT CONCAT('ALTER TABLE ',db,'.',old_tblname,' RENAME ',db,'.',new_tblname,';') FROM (SELECT table_schema db,table_name old_tblname,substr(table_name,4) FROM information_schema.tables WHERE SUBSTR(table_name,1,3)='dr_') A" > BigRename.sql
mysql -u... -p... < BigRename.sql

Answer 2

In phpMyAdmin Version information: 4.3. upward. Click on the database in question. It shows the structure and should shows or list all the tables.

Scroll down to the bottom and click 'CHECK ALL' to check all the tables in the database.

Then click on the Object Creation Options, scroll to REPLACE TABLE PREFIX and click this. Then run the query by clicking GO! It is done.

I discover this after series of trials and solve a longtime problem.