How I can change prefixes in all tables in my MySQL DB?

My provider installed to my site Drupal CMS. Now I开发者_JAVA技巧 need copy all my data from old site. I have tables without prefixes in my old DB, but in new DB all tables have dp_[table_name] prefix.

zerkms solution didn't work for me. I had to specify the information_schema database to be able to query the Tables table.

SELECT 
    CONCAT('RENAME TABLE ', GROUP_CONCAT('`', TABLE_SCHEMA, '`.`', TABLE_NAME, '` TO `', TABLE_SCHEMA, '`.`prefix_', TABLE_NAME, '`')) AS q
FROM 
    `information_schema`.`Tables` WHERE TABLE_SCHEMA='test';

Edit:

Optimized the query to only call RENAME TABLE once. Something I walked into was the fact that the concatenated output got truncated at 341 characters. This can be solved (if allowed by your server) by setting the MySQL variable group_concat_max_len to a higher value:

SET group_concat_max_len = 3072; -- UTF8 assumes each character will take 3 bytes, so 3072/3 = 1024 characters.

PhpMyAdmin allows you to do this now. At the "Database" level select the Structure tab to see all the tables. Click 'check all' (below the table listing). On the 'With selected' dropdown choose: 'Replace table prefix'.

write a script that will run RENAME TABLE for each table.

SELECT 
  GROUP_CONCAT('RENAME TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` TO `', TABLE_SCHEMA, '`.`prefix_', TABLE_NAME, '`;' SEPARATOR ' ')
FROM 
  `TABLES` WHERE `TABLE_SCHEMA` = "test";

where "test" is expected database name

after this you can long query that will add prefixes if you execute it ;-)

You can simply dump the database, open the dump with a text editor, replace all occurrences of "CREATE TABLE " with "CREATE TABLE dp_" and restore the database. It takes a couple of minutes to do.

Just modded this slightly to account for situations where the prefix is also in the table name.

SET @database   = "database_name"; 
SET @old_prefix = "old_prefix_"; 
SET @new_prefix = "new_prefix_";
   SELECT
    CONCAT(
        "RENAME TABLE ",
        TABLE_NAME,
        " TO ",
        CONCAT(@new_prefix, TRIM(LEADING @old_prefix FROM TABLE_NAME)),
        ';'
    ) AS "SQL" FROM information_schema.TABLES WHERE TABLE_SCHEMA = @database;

If there's someone out there yet wondering how to do this (as it did not work form me the other options) you can run this (changing the first three variables for your values, of course):

SET @database   = "database_name"; 
SET @old_prefix = "old_prefix_"; 
SET @new_prefix = "new_prefix_";
   SELECT
    concat(
        "RENAME TABLE ",
        TABLE_NAME,
        " TO ",
        replace(TABLE_NAME, @old_prefix, @new_prefix),
        ';'
    ) AS "SQL" FROM information_schema.TABLES WHERE TABLE_SCHEMA = @database;

And then you will be prompted with a bunch of queries needed in order to change all the tables in your database. You simply have to copy that, run it and voilá!

SET @database   = "Database-Name-Here";
SET @old_prefix = "wp_";
SET @new_prefix = "ab_";
   SELECT
    CONCAT(
        "RENAME TABLE ",
        @database,
        ".",
        TABLE_NAME,
        " TO ",
        @database,
        ".",
        CONCAT(@new_prefix, TRIM(LEADING @old_prefix FROM TABLE_NAME)),
        ';'
    ) AS "SQL" FROM information_schema.TABLES WHERE TABLE_SCHEMA = @database;

Replace "Database-Name-Here" with the name of your database, "wp_" with the old prefix, and "ab_" with the new prefix. The preceding code will produce queries for the MySQL DB, also, the queries created by the above code are surrounded by single quotes, which must be replaced. However, if you are dealing with WordPress, you will need to do some more steps otherwise your site will be broken. Following the queries created by the aforementioned code, the following queries must be executed.

update NEWPREFIX_usermeta set meta_key = 'NEWPREFIX_capabilities' where meta_key = 'OLDPREFIX_capabilities';
update NEWPREFIX_usermeta set meta_key = 'NEWPREFIX_user_level' where meta_key = 'OLDPREFIX_user_level';
update NEWPREFIX_usermeta set meta_key = 'NEWPREFIX_autosave_draft_ids' where meta_key = 'OLDPREFIX_autosave_draft_ids';
update NEWPREFIX_options set option_name = 'NEWPREFIX_user_roles' where option_name = 'OLDPREFIX_user_roles'

Replace "NEWPREFIX" with your new prefix and "OLDPREFIX" with your old prefix.