DROP all system tables

[apmuthu]

Created a Wiki Article on the topic.

Multi table DROP based on pattern of table name is not available in MySQL. The workaround is:

Code: Select all

SET SESSION group_concat_max_len = 1000000;
SET @schema:='nubuilder4';
SET @string:='zzzzsys_%';
 
SELECT CONCAT ('DROP TABLE ',GROUP_CONCAT(DISTINCT CONCAT(@schema,'.',TABLE_NAME) SEPARATOR ", "),';')
INTO @droptool
FROM information_schema.TABLES
WHERE @schema = DATABASE() AND TABLE_TYPE="BASE TABLE"
AND TABLE_NAME LIKE @string;
 
SELECT @droptool;
 
PREPARE stmt FROM @droptool;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

On next login, nubuilder4.sql is used to populate it with the System Tables. Useful when you want all developmental work reset to new code from the repo whilst retaining the user tables and their data.

[apmuthu]

As this is a very dangerous operation, a warning is provided for the inexperienced user.

[apmuthu]

Updated to include Views as well and purge variable after use.

Code: Select all

SET SESSION group_concat_max_len = 1000000;
SET @schema:='nubuilder4'; -- change as needed
SET @string:='zzzzsys_%';
 
SELECT CONCAT ('DROP TABLE ',GROUP_CONCAT(DISTINCT CONCAT(@schema,'.',table_name) SEPARATOR ", "),';')
INTO @droptool
FROM information_schema.tables
WHERE @schema = DATABASE() AND TABLE_TYPE = "BASE TABLE"
AND table_name LIKE @string;

SELECT @droptool;
 
PREPARE stmt FROM @droptool;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET @droptool:=NULL;

SELECT CONCAT ('DROP VIEW ',GROUP_CONCAT(DISTINCT CONCAT(@schema,'.',table_name) SEPARATOR ", "),';')
INTO @droptool
FROM information_schema.tables
WHERE @schema = DATABASE() AND TABLE_TYPE = "VIEW"
AND table_name LIKE @string;

SELECT @droptool;
 
PREPARE stmt FROM @droptool;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET @droptool:=NULL;