Script to drop all objects from MySQL or MariaDB

Script to drop all tables and index from MySQL or MariaDB

SET FOREIGN_KEY_CHECKS = 0;
SET GROUP_CONCAT_MAX_LEN=5000000;
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;

Script to drop all Views from MySQL and MariaDB

SET GROUP_CONCAT_MAX_LEN=5000000;
SET @view = NULL;
select GROUP_CONCAT('`', table_name, '`') INTO @view from information_schema.views  WHERE table_schema = (SELECT DATABASE());
SELECT IFNULL(@view,'dummy') INTO @view;
SET @view = CONCAT('DROP VIEW IF EXISTS ', @view);
PREPARE stmt FROM @view;
EXECUTE stmt;

Script to drop all procedures from MySQL or MariaDB:


/* Create the procedure for drop the function */
DELIMITER $$
Create procedure drop_routines() 
BEGIN
DECLARE v_routine_name varchar(500);
DECLARE finished INT DEFAULT 0;
DECLARE curName CURSOR FOR select  routine_name 
from information_schema.routines WHERE routine_schema = (SELECT DATABASE()) 
and routine_type = 'PROCEDURE' and routine_name <> 'drop_routines';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
OPEN curName;
 getExit: LOOP
		FETCH curName INTO v_routine_name;
		IF finished = 1 THEN LEAVE getExit;
        END IF;
        SET v_routine_name = CONCAT('DROP PROCEDURE IF EXISTS ', v_routine_name);
	    PREPARE stmt FROM v_routine_name;
		EXECUTE stmt;
	END LOOP getExit;
CLOSE curName;
end $$
DELIMITER ;
/* Call the DROP Procedure for delete the Procedure */
call drop_routines();
/* drop the procedure which created */
drop procedure drop_routines;

Script to drop all functions from MySQL or MariaDB:

/* Create the procedure for drop the function*/
DELIMITER $$
Create procedure drop_routines() 
BEGIN
DECLARE v_routine_name varchar(500);
DECLARE finished INT DEFAULT 0;
DECLARE curName CURSOR FOR select  routine_name 
from information_schema.routines WHERE routine_schema = (SELECT DATABASE()) 
and routine_type = 'FUNCTION' and routine_name <> 'drop_routines';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
OPEN curName;
 getExit: LOOP
		FETCH curName INTO v_routine_name;
		IF finished = 1 THEN LEAVE getExit;
        END IF;
        SET v_routine_name = CONCAT('DROP FUNCTION IF EXISTS ', v_routine_name);
	    PREPARE stmt FROM v_routine_name;
		EXECUTE stmt;
	END LOOP getExit;
CLOSE curName;
end $$
DELIMITER ;
/* Call the DROP Procedure for delete the Procedure */
call drop_routines();
/* drop the procedure which created */
drop procedure drop_routines;
Advertisement

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.