Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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
add comment

1 Answer

up vote 6 down vote accepted

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
share|improve this answer
    
Thanks, I'll check this tomorrow. It is very late here... –  JVerstry Feb 16 '12 at 21:48
    
@RolandoMySQLDBA: Will this work if you have FOREIGN KEY constraints defined? –  ypercube Feb 17 '12 at 0:30
    
No, probably not. –  RolandoMySQLDBA Feb 17 '12 at 0:32
    
@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 2:50
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.