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 184.108.40.206 and 220.127.116.11.
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?
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 ;