How drop multiple tables in MySQL

To create DROP statements for all table begins with 'prefix_' from database 'my_database':

> SELECT CONCAT('DROP TABLE',table_name,';') FROM information_schema.TABLES 
    WHERE table_schema = 'my_database' AND table_name LIKE 'prefix_%';

Or better yet, create a one-liner:

> SELECT CONCAT('DROP TABLE',GROUP_CONCAT(table_name),';') FROM information_schema.TABLES 
    WHERE table_schema = 'my_database' AND table_name LIKE 'prefix_%';

Example of creating store procedure:

mysql> delimiter $$
mysql> create procedure drop_tables_like(pattern varchar(255), db varchar(255))
    -> begin
    -> select @str_sql:=concat('drop table', group_concat(table_name))
    -> from information_schema.tables where table_schema=db and table_name like pattern;
    -> prepare stmt from @str_sql;
    -> execute stmt;
    -> drop prepare stmt;
    -> end$$
mysql> call drop_tables_like('prefix_','my_database')$$
mysql> drop procedure if exists drop_tables_like$$
mysql> delimiter;

Advanced Batch Commands: Command Line Parameters

Starting from Windows NT 4, command line parameters can be retrieved in the following ways.

• %CmdCmdLine%
  Returns the entire command line as passed to CMD.EXE.  It leaves all delimiters intact, except, in Windows 2000 and later, leading spaces before the first argument.
• %*
Returns the remainder of the command line starting from the first argument.  It also leaves all delimiters intact, except, in Windows 2000 and later, leading spaces before the first argument.
• %~dn
Returns the drive letter of %n (n can range from 0 to 9) if %n is a valid path or file name (no UNC)
• %~pn
  Returns the directory of %n if %n is a valid path or file name (no UNC)
• %~nn
  Returns the file name only of %n if %n is a valid file name
• %~xn
  Returns the file extension only of %n if %n is a valid file name
• %~fn
Returns the fully qualified path of %n if %n is a valid file name or directory

To remove the leading space of %* included by NT 4 use the following commands:

    SET commandline=%*

    IF NOT CMDEXTVERSION 2 SET commandline=%commandline:~1%