DROP all System tables in the database

System Tables

• All System tables are created during first login and then the login screen appears.
• These tables can be seen with SHOW TABLES LIKE "zzzzsys_%"

User Tables

• Over time, many user tables are created.
• The System Tables naming prefix ensures that in general all User Tables are alphabetically first.

Resetting the NuBuilder Installation

Unless you know what you are doing, do not attempt this. Without a proper backup, you could end up destroying all user generated records in the system tables. This is meant for developers who wish to reset their system for iterative development only. YOU HAVE BEEN SUFFICIENTLY WARNED!!!

Clicking the system Update within the NuBuilder user interface preserves the user generated records in the System Tables. The nubuiler4.sql is the source for all System Tables and their records.

But, if we wish to purge all the user development records as well, we need to drop all the System Tables and recreate them like in a new installation whilst preserving the User Tables and their data.

• This process ensures that all development work gets deleted too as records in the System Tables get deleted.
• All System Tables and Views are to be dropped with (MySQL does not have a multi table pattern based DROP statement yet).

The Code

SET SESSION group_concat_max_len = 1000000;
SET @schema:=DATABASE(); -- choose DB
SET @string:='zzzzsys_%';
SET @droptool:='';

-- select @schema;

SELECT CONCAT ('DROP TABLE ',GROUP_CONCAT(DISTINCT CONCAT(@schema,'.',table_name) SEPARATOR ", "),';')
INTO @droptool
FROM information_schema.tables
WHERE TABLE_SCHEMA = BINARY @schema AND TABLE_TYPE = "BASE TABLE"
AND TABLE_NAME LIKE @string;

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

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

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

• On next login, all System Tables and Views are restored from the nubuilder4.sql file and fresh development can begin with all User Tables intact.