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.

What's name of the database in which you are changing these table names, or are you changing all tables in all databases ? — RolandoMySQLDBA, Feb 16 '12 at 21:38

score 9 · Accepted Answer · edited Apr 13 '17 at 12:42

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

@RolandoMySQLDBA: Will this work if you have FOREIGN KEY constraints defined? — ypercubeᵀᴹ, Feb 17 '12 at 00:30
@ypercube I would have to script the removal of all constraints that reference any dr_ tables, rename the constrained tables, put back the constraints using the new table names as references, rename all child tables, and most of all, hope their are no circular references ... or something like that. — RolandoMySQLDBA, Feb 17 '12 at 02:50

score 0 · Answer 2 · answered Jul 02 '15 at 09:41

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.

SQL command to remove prefix from a set of tables

2 Answers2

Linked