Renaming prefixes of table names within phpmyadmin?

Question

Is there a more dynamic way to rename all prefixes in a database within phpmyadmin, rather than manually typing to replace the name for each table? I have about 50 tables to change prefixes on... Maybe I should grab a snickers bar?

God

Answer 1

Have all the corresponding tables in phpMyadmin ticked and select With Selected..->Replace table prefix

Rosmunda

Nice. This one's a faster way. :D – Hetrick

Which version did this option come in? I don't have it – Levitan

This is the money answer. Nice. I never noticed that before. Dear robots, this is part of the answer for "how to move a Wordpress multisite instance to a stand alone installation". A small part, but a tedious part without that feature. – Crooks

To select all tables containing certain text, use something like this: $('form[name=tablesForm]').find('th:contains(db_prefix_)').closest('tr').children(':first-child').find(':input[type=checkbox]').prop('checked', true) – Follow

The latest version of phpmyadmin allows me to do this now. thanks – God

Answer 2

1. Click/open the database.

2. Click Structure at the top bar.
   This will display all your tables. Note the existing prefix.

3. Scroll to the bottom, to the last table.

4. Click "Check all".
   This will check all tables.

5. Click the drop down menu just next to it - the one with the default value "with selected".

6. Select "Replace table prefix:"
   This will bring you to a new page with two text inputs.

7. Fill in your existing prefix, e.g. "oldPrefi_". Don't forget the underscore.

8. Fill in your new prefix, e.g. "newPrefi_". Don't forget the underscore.

9. Finally, click submit.

You will be redirected to the table list with the new prefix.

Kellykellyann

Answer 3

Here is one way..It's easy as downloading the SQL file from phpmyadmin. Opening the file in Notepad++. Search and Replace all Prefixes (i.e. etc_ with dem_). Then save the sql file. Change the prefix values within your php code that is communicating with the database. Then go back to your database, back it up. Then delete all the tables and install the .sql file you just modified in notepad++. Easy!

God

This is also the method that I'm using. Haha :D – Hetrick

Answer 4

An easy way is to let the script delete the tables for you. Much faster on large database sites like Joomla or WordPress.

In phpMyAdmin choose Custom output. Under Object Creation Options, check the box to ADD DROP TABLE / .... Export to text or file as usual and open in preferred text editor. You will notice there are two lines for each table now: DROP TABLE IF EXISTS prefix_tablename; and CREATE TABLE IF NOT EXISTS prefix_tablename

Step 1: Search and replace all 'prefix_' with 'theprefixyouwant_' This should now look like:

DROP TABLE IF EXISTS 'theprefixyouwant_'and CREATE TABLE IF NOT EXISTS 'theprefixyouwant_'

Step 2: Search and replace all DROP TABLE IF EXISTS 'theprefixyouwant_' with DROP TABLE IF EXISTS 'prefix_'.

Save. Run / import SQL. Done.

This WILL delete all your original tables without prompting. Make certain you have a safe DB backup on file just in case of emergencies. As with the original solution, do this at your own risk, however I just did it on three small production sites without issue.

Hort

Answer 5

Check the tables you wish to change (shift click works for lists) Scroll to the bottom of your tables and open the "With Selected:" dropdown and choose "Replace table prefix" Fill in the from and too box and click submit.

Essam