Delete common prefix of all tables in DB in phpMyAdmin

Question

All my tables have the prefix bn_. For example, a table would be named bn_blast.

Any SQL command to run that will delete all the prefixes from all the tables?

What with stored procedures/functions/foreign key constraints that depends on that tables? — Lukasz Szozda, Nov 18 '15 at 14:24
What? Sorry, I don't know this field enough to understand you well... — , Nov 18 '15 at 14:34
I guess you have table like `bn_customer` and you want to rename it to `customer`. It is not so simply since many other database object can depend on it — Lukasz Szozda, Nov 18 '15 at 14:36
Hello, indeed that's what I need... No way to do so for all at once? For all tables (and the objects in them) at once? Ben, — , Nov 18 '15 at 14:50

score 1 · Accepted Answer · edited Jun 13 '16 at 04:38

It is a one-liner.

In the console:

mysql -u root -p -AN -e" select concat('RENAME TABLE ', concat(table_name, concat(' TO ', concat(substr(table_name,4 ), ';')))) from information_schema.tables where table_schema='db_name' " > renaming.sql

Enter password when prompted.

Open the "renaming.sql" file, in the first line, add "use db_name;" and then save. Then, do the following:

mysql -u root -p < renaming.sql

Enter password when prompted.

Now, check your tables:

show tables;

Output: Before

mysql> show tables;
+---------------------+
| Tables_in_STACK     |
+---------------------+
| db_answer           |
| db_answers          |
| db_circle           |
| db_fee              |
| db_housing          |
| db_im_originals     |
| db_im_savegroups    |
| db_im_savespecs     |
| db_location_share   |
| db_order1           |
| db_orderitems       |
| db_patientinfo      |
| db_quest            |
| db_share            |
| db_t2               |
| db_tbdatabaseerrors |
| db_tblchanges       |
| db_test             |
| db_test1            |
| db_test_fid         |
| db_test_table       |
| db_testing          |
| db_user             |
+---------------------+

Output: After

mysql> show tables;
+-------------------+
| Tables_in_STACK   |
+-------------------+
| answer            |
| answers           |
| circle            |
| fee               |
| housing           |
| im_originals      |
| im_savegroups     |
| im_savespecs      |
| location_share    |
| order1            |
| orderitems        |
| patientinfo       |
| quest             |
| share             |
| t2                |
| tbdatabaseerrors  |
| tblchanges        |
| test              |
| test1             |
| test_fid          |
| test_table        |
| testing           |
| user              |
+-------------------+

This was excellent, and combined with the answer https://stackoverflow.com/a/14889163/4844269 you can also uppercase the first character of each table name if you like. Thus: ` mysql -u root -p -AN -e" select concat('RENAME TABLE ', concat(table_name, concat(' TO ', concat(concat(upper(substr(table_name,5, 1 )), lower(substr(table_name from 6))), ';')))) from information_schema.tables where table_schema='SCHEMA' " > SCHEMArenaming.sql ` — Xaraxia, May 25 '17 at 03:07

score 1 · Answer 2 · answered May 28 '16 at 03:56

To remove a common prefix for all tables in a MySQL database using phpMyAdmin:

Select the database.
Go to the "Structure" tab.
Scroll to the bottom, and click the "Check All" checkbox. This selects all tables.
In the "With selected:" dropdown next to it, select "Replace table prefix". This takes you to the "Replace table prefix" page.
In the "From" field, type in the prefix that you want to remove, such as shop_.
In the "To" field, leave it empty.
Click "Submit"... and boom! Done!

You can add or rename prefixes in a similar way.

Tip: if your database has lots of other tables and you want to select only the tables containing a certain string, you can make use of the debug JavaScript console. For example, to select all tables containing shop_, run this:

$('form[name=tablesForm]').find('th:contains(shop_)').closest('tr').children(':first-child')
    .find(':input[type=checkbox]').prop('checked', true)

Delete common prefix of all tables in DB in phpMyAdmin

2 Answers2