mySQL drop tables with Wildcard using only SQL statement?

Question

Seen a lot for dropping tables using a wildcard but not a direct SQL statement except this one:

http://azimyasin.wordpress.com/2007/08/11/mysql-dropping-multiple-tables/

It says:

SHOW TABLES LIKE ‘phpbb_%’;

then DROP TABLES, is there a neat way to combine this all into one SQL Statement?

Answer 1

You could use dynamic SQL to do it, inside a stored procedure. It'd look something like this (untested):

CREATE PROCEDURE drop_like (IN pattern VARCHAR(64))
BEGIN
  DECLARE q tinytext;
  DECLARE done INT DEFAULT FALSE;
  DECLARE cur CURSOR FOR
    SELECT CONCAT('DROP TABLE "', table_schema, '"."', table_name, '"')
    FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_name LIKE pattern;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  OPEN cur;

  drop_loop: LOOP
    FETCH cur INTO q;
    IF done THEN
      LEAVE drop_loop;
    END IF;
    PREPARE stmt FROM @q;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END LOOP;
  CLOSE cur;
END;

Answer 2

Using dynamic SQL in a query, as per derobert's answer, is the only to do this with pure SQL (no app code).

I wrote a generalized procedure to do this sort of thing (run a query for every table in a database) that you can find here - to use it, you would just need to run this query:

CALL p_run_for_each_table('databasename', 'DROP TABLE `{?database}`.`{?table}`');

It works in essentially the same way as derobert's answer.

However, the writer of that blog post was probably expecting you to write app code to turn the names of tables into a single DROP statement.

To do this, you would iterate over the results of the SHOW TABLE in your code and build a single query like this:

DROP TABLE table1, table2, tablewhatever;

Answer 3

This can be achieved via stored procedure, for example:

CREATE DEFINER=`some_user`@`%` PROCEDURE `drop_tables`()
LANGUAGE SQL
NOT DETERMINISTIC
MODIFIES SQL DATA
SQL SECURITY DEFINER
COMMENT ''
BEGIN

#We need to declare a variable with default 0 to determine weather to continue the loop or exit the loop.
DECLARE done INT DEFAULT 0;
DECLARE archive_table_name VARCHAR(100);

#Select desired tables from `information_schema`
DECLARE cur CURSOR FOR
    SELECT t.`TABLE_NAME` FROM information_schema.`TABLES` t WHERE  t.`TABLE_NAME`  LIKE 'some_table_name%'
    AND t.CREATE_TIME BETWEEN DATE_SUB(NOW(), INTERVAL 9 MONTH) AND DATE_SUB(NOW(), INTERVAL 6 MONTH);

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN cur;
read_loop: LOOP

    #Fetch one record from CURSOR and set variable (if not found, then variable `done` will be set to 1 by continue handler)
    FETCH cur INTO archive_table_name;    
     
     IF done THEN
        LEAVE read_loop;  #If done is set to 1, then exit the loop, else continue
    END IF;
 
    #Do your work
    -- Create the truncate query
    SET @s = CONCAT('DROP TABLE IF EXISTS ', archive_table_name);
    -- Prepare, execute and deallocate the truncate query
    PREPARE drop_statement FROM @s;
    EXECUTE drop_statement;
    DEALLOCATE PREPARE drop_statement;
    

END LOOP;
CLOSE cur; #Closing the cursor

END

Pay attention to the database user, which is creating/executing the stored routine: it must have appropriate credentials for executing/dropping tables.