MySQL - Drop tables with common prefix

I needed to delete multiple tables with name like 'TMP_%' the following procedure do this job in an effective way !

DELIMITER $$

DROP PROCEDURE IF EXISTS curdemo;

CREATE PROCEDURE curdemo()
BEGIN

DECLARE name_val VARCHAR(255);
DECLARE no_more_rows boolean;
DECLARE num_rows INT;

DECLARE friends_cur CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_name like 'TMP_%';

-- Declare 'handlers' for exceptions
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET no_more_rows = TRUE;


-- 'open' the cursor and capture the number of rows returned
-- (the 'select' gets invoked when the cursor is 'opened')
OPEN friends_cur;
select FOUND_ROWS() into num_rows;

the_loop: LOOP

FETCH friends_cur
INTO name_val;

IF no_more_rows THEN
CLOSE friends_cur;
LEAVE the_loop;
END IF;

SET @d=concat('DROP TABLE IF EXISTS ',name_val);
PREPARE prst1 FROM @d;
EXECUTE prst1;


END LOOP the_loop;

END;

CALL curdemo();

Share on Google Plus

About Vittorio Pavesi