Last active
August 29, 2015 14:11
-
-
Save m-radzikowski/2c75db3fe237d2a1c0d2 to your computer and use it in GitHub Desktop.
Drops all tables, views and routines in database. Based on http://stackoverflow.com/a/18625545/2512304
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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