Drop MySQL Tables by Table Prefix

Problem:

I’ve access to only 1 MySQL database, but I have multiple Magento Installations in it – so naturally, I’m using Table Prefixes to keep them separated. In my case, I have Magento Community Editions 1.6.2.0 and 1.7.0.2.

My table prefixes are ce1620_ and ce1702_, which is simple and easy to understand. The problem is how can I simply remove all tables for only 1 installation?

Solution:

The answer came in the form of a stored procedure. Most of the heavy lifting was done by a nice poster over on StackOverflow.

My little extra ingredient was to disable foreign key constraints, so I could obliterate the whole darn thing in one pass.

As always, be careful and back up before doing something as drastic as this.

DROP PROCEDURE IF EXISTS droplike;
delimiter //
CREATE PROCEDURE droplike(pattern VARCHAR(20))
BEGIN
 SET foreign_key_checks = 0;
 SET group_concat_max_len = 65535;
 SELECT @DROP:= concat( 'drop table ', group_concat(TABLE_NAME) , ';' ) FROM information_schema.tables WHERE table_schema = "YOUR_DATABASE_NAME_HERE" AND TABLE_NAME LIKE pattern;
 PREPARE statement FROM @DROP;
 EXECUTE statement;
 SET foreign_key_checks = 1;
END //
delimiter ;

Facebook comments: