Benia Benia - 6 months ago 14
SQL Question

Delete common prefix of all tables in DB in phpMyAdmin

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?

Answer

It is a onliner

in 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 and in first line add "use db_name;" and then save then do the below:

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              |
+-------------------+
Comments