Remove all tables from database MySql
SET FOREIGN_KEY_CHECKS = 0; SET GROUP_CONCAT_MAX_LEN=32768; SET @tables = NULL; SELECT GROUP_CONCAT('`', table_name, '`') INTO @tables FROM information_schema.tables WHERE table_schema = (SELECT DATABASE()); SELECT IFNULL(@tables,'dummy') INTO @tables; SET @tables = CONCAT('DROP TABLE IF EXISTS ', @tables); PREPARE stmt FROM @tables; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET FOREIGN_KEY_CHECKS = 1;
This script will not raise error with NULL result in case when you already deleted all tables in the database by adding at least one nonexistent – “dummy” table.
And it fixed in case when you have many tables.
And This small change to drop all view exist in the Database
SET FOREIGN_KEY_CHECKS = 0; SET GROUP_CONCAT_MAX_LEN=32768; SET @views = NULL; SELECT GROUP_CONCAT('`', TABLE_NAME, '`') INTO @views FROM information_schema.views WHERE table_schema = (SELECT DATABASE()); SELECT IFNULL(@views,'dummy') INTO @views; SET @views = CONCAT('DROP VIEW IF EXISTS ', @views); PREPARE stmt FROM @views; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET FOREIGN_KEY_CHECKS = 1;