CODEWITHSUNDEEP
mysql
Amit Shah
Amit Shah

Reputation: 1380

Remove prefix from all mysql table names of a database

*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

Upvotes: 1

Views: 1281

Answers (1)

1000111
1000111

Reputation: 13519

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 //

Upvotes: 1

Related Questions