Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to add prefix of all tables in mysql

Tags:

mysql

rename

How can I add prefix to all tables in mysql using query.

For example:

I need to add "dr_" in all tables which are available in mysql database.

like image 975
Fero Avatar asked Nov 01 '11 18:11

Fero


People also ask

How do I find the table prefix in MySQL?

To list all tables with some prefix, "any number of symbols" wildcard ( % ), should be used. _ is also a wildcard, representing any single symbol, and therefore it should be escaped.

How can I get a list of all tables in MySQL?

MySQL SHOW TABLES command example To use the SHOW TABLES command, you need to log on to the MySQL server first. On opening the MySQL Command Line Client, enter your password. Select the specific database. Run the SHOW TABLES command to see all the tables in the database that has been selected.


2 Answers

Run all queries that you get from running this query:

SELECT Concat('ALTER TABLE `', TABLE_NAME, '` RENAME TO `dr_', TABLE_NAME, '`;') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '<name of your db>';
like image 182
Abhay Avatar answered Sep 28 '22 06:09

Abhay


For this example, I will create a database called prefixdb with 4 tables:

mysql> drop database if exists prefixdb;
Query OK, 4 rows affected (0.01 sec)

mysql> create database prefixdb;
Query OK, 1 row affected (0.00 sec)

mysql> use prefixdb
Database changed
mysql> create table tab1 (num int) ENGINE=MyISAM;
Query OK, 0 rows affected (0.05 sec)

mysql> create table tab2 like tab1;
Query OK, 0 rows affected (0.05 sec)

mysql> create table tab3 like tab1;
Query OK, 0 rows affected (0.05 sec)

mysql> create table tab4 like tab1;
Query OK, 0 rows affected (0.04 sec)

mysql> show tables;
+--------------------+
| Tables_in_prefixdb |
+--------------------+
| tab1               |
| tab2               |
| tab3               |
| tab4               |
+--------------------+
4 rows in set (0.00 sec)

The query to generate it would be

select
    concat('alter table ',db,'.',tb,' rename ',db,'.',prfx,tb,';')
from
    (select table_schema db,table_name tb
    from information_schema.tables where
    table_schema='prefixdb') A,
    (SELECT 'dr_' prfx) B
;

Running it at the command line I get this:

mysql> select concat('alter table ',db,'.',tb,' rename ',db,'.',prfx,tb,';') from (select table_schema db,table_name tb from information_schema.tables where table_schema='prefixdb') A,(SELECT 'dr_' prfx) B;
+----------------------------------------------------------------+
| concat('alter table ',db,'.',tb,' rename ',db,'.',prfx,tb,';') |
+----------------------------------------------------------------+
| alter table prefixdb.tab1 rename prefixdb.dr_tab1;             |
| alter table prefixdb.tab2 rename prefixdb.dr_tab2;             |
| alter table prefixdb.tab3 rename prefixdb.dr_tab3;             |
| alter table prefixdb.tab4 rename prefixdb.dr_tab4;             |
+----------------------------------------------------------------+
4 rows in set (0.00 sec)

Pass the result back into mysql like this:

mysql -hhostip -uuser -pass -AN -e"select concat('alter table ',db,'.',tb,' rename ',db,'.',prfx,tb,';') from (select table_schema db,table_name tb from information_schema.tables where table_schema='prefixdb') A,(SELECT 'dr_' prfx) B" | mysql -hhostip -uuser -ppass -AN

With regard to your question if you want to rename all tables, do not touch information_schema and mysql databases. Use this query instead:

select
    concat('alter table ',db,'.',tb,' rename ',db,'.',prfx,tb,';')
from
    (select table_schema db,table_name tb
    from information_schema.tables where
    table_schema not in ('information_schema','mysql')) A,
    (SELECT 'dr_' prfx) B
;

Give it a Try !!!

like image 39
RolandoMySQLDBA Avatar answered Sep 28 '22 05:09

RolandoMySQLDBA