Remove prefix from all mysql table names of a database

Question

*I want to remove prefix from all table names in MySQL

I have a test database with more than 100 tables, so i don't want to go through a manual process or renaming each table.

e.g ci_categories ci_products

expected output: categories products

i want to remove prefix from all table, that is ci_

Is there a MySQL query to achieve this?

Thanks Amit

Answer 1

I've written a mysql stored procedure in order to change the table names since there's no other straight forward way to change all the table names through a single query.

delimiter //
CREATE  PROCEDURE `ChangeTableNameProcedure`()
BEGIN

    DECLARE int_val INT DEFAULT 0;
    DECLARE my_outer_cursor_done INT DEFAULT FALSE; 
    DECLARE my_oldTable VARCHAR(100);
    DECLARE my_newTable VARCHAR(100);

    DECLARE tableNameCursor CURSOR FOR SELECT TABLE_NAME oldName,
    CONCAT(SUBSTRING(TABLE_NAME,POSITION('ci_' IN TABLE_NAME) + 3)) newName
    FROM information_schema.tables 
    WHERE TABLE_SCHEMA = 'YOUR_DATABASE_NAME' AND TABLE_NAME LIKE 'ci_%';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET my_outer_cursor_done = TRUE;



OPEN tableNameCursor;
    OUTER_CURSOR_LOOP: LOOP
            FETCH FROM tableNameCursor INTO my_oldTable,my_newTable;

                    IF my_outer_cursor_done THEN
                        CLOSE tableNameCursor;
                        LEAVE OUTER_CURSOR_LOOP;
                    END IF;
                    SET @old = my_oldTable;
                    SET @new = my_newTable;
                    SET @statement = CONCAT('RENAME TABLE ',@old,' TO ',@new);

                    PREPARE stmt FROM @statement;
                    EXECUTE stmt;
                    DEALLOCATE PREPARE stmt;


                    END LOOP OUTER_CURSOR_LOOP;


    END//

N:B:

1. I've assumed that all the table names in your database has a prefix ci_.
2. You have to put your database name in TABLE_SCHEMA= 'YOUR_DATABASE_NAME' clause.
3. Note that + 3 stands for the new table name where first three characters will be skipped (if your old table name is 'ci_old_table' then your new table name would be 'old_table'. Note that 'ci_' first three characters have been skipped in new table name.)
4. After creating the procedure you need to call it simply by its name. The syntax is Call ChangeTableNameProcedure();
5. Please make sure while creating the procedure you are not ignoring the delimeter //