Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Drops all tables, views and routines in database. Based on http://stackoverflow.com/a/18625545/2512304
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
-- drop tables
SET @tables = NULL;
SELECT GROUP_CONCAT('`', table_name, '`') INTO @tables
FROM information_schema.tables
WHERE table_schema = (SELECT DATABASE()); -- from currently selected schema
SELECT IFNULL(@tables, 'dummy') INTO @tables; -- to prevent error when there are already no tables
SET @tables = CONCAT('DROP TABLE IF EXISTS ', @tables);
PREPARE stmt FROM @tables;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- drop views
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;
-- drop routines (functions and procedures)
DELETE FROM mysql.proc WHERE db = (SELECT DATABASE()) AND (type = 'PROCEDURE' OR type = 'FUNCTION');
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment