How to change all table prefix in a single query

Question

I am pretty amateur in mysql..can you please tell me how can I change table prefixes of my whole database in a single query... I can do it manually, but its quite time consuming to change all the tables prefixes. Please help me out. Like isc_administrator_log to cus_administrator_log means isc_ to cus_

I found these two solutions but do not understand either of them.

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

and

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';

Why not just do one, modify table statement in a text editor, copy and paste, then run the script. Your question is interesting, however. — Pete B., May 02 '13 at 13:48
@Pete Bel i cant understand those queries hence i put the question..if u understand plz share ur views...i ran those queries..but in both case it returned NULL — Kingshuk Deb, May 02 '13 at 13:56

score 2 · Answer 1 · answered May 12 '17 at 19:42

2

phpmyadmin : select database ; tab structure => Check all => (with selected list) select add prefix to table .

(is not query but it works)

answered May 12 '17 at 19:42

Nicolai

21
2

score 1 · Answer 2 · answered May 02 '13 at 13:59

1

Both statements generate strings which happen to be SQL statements that you can then copy/paste at your prompt to rename your tables.

Just replace the following two strings with the actual values your need:

prefix_ : the prefix you want to add

'test' : the name of the database containing the tables you want to rename

Both statements are almost identical, the only difference is that information_schema is not explicitely mentioned in the first statement. Therefore the first statement must be run from the information_schema database (issue USE information_schema beforehands).

answered May 02 '13 at 13:59

RandomSeed

29,301
6
52
87

after giving this it returned RENAME TABLE `iblue_bh_db`.`bh_admin` TO `iblue_bh_db`.`blue_bh_admin`,`iblue_bh_db`.`bh_album` TO `iblue_bh_db`.`blue_bh_album`,`iblue_bh_db`.`bh_banner` TO `iblue_bh_db`.`blue_bh_banner`,`iblue_bh_db`.`bh_blog` TO `iblue_bh_db`.`blue_bh_blog`,`iblue_bh_db`.`bh_blog_catagory` TO `iblue_bh_db`.`blue_bh_blog_catagory`,`iblue_bh_db`.`bh_blog_tags` TO `iblue_bh_db`.`blue_bh_blog_tags` – Kingshuk Deb May 02 '13 at 14:18

score 0 · Accepted Answer · edited May 23 '17 at 11:54

You can do Dynamic SQL Query For MySQL 5.0.13


delimiter // 
CREATE PROCEDURE dynamic(IN tbl CHAR(64), IN col CHAR(64))
BEGIN
    SET @s = CONCAT('SELECT 'RENAME TABLE ', 
             GROUP_CONCAT('', TABLE_SCHEMA, ''.'', TABLE_NAME,
             ' TO ', TABLE_SCHEMA, ''='.prefix_''', TABLE_NAME, '')) AS q
             FROM 
             information_schema.Tables WHERE TABLE_SCHEMA='test'';;'
    PREPARE stmt FROM @s;
    EXECUTE stmt;
END
//
delimiter ;

Got the Reference from here

How to change all table prefix in a single query

3 Answers3

Linked