How do I drop all empty tables from a MySQL database?

This stored procedure should do it:

DELIMITER $$

DROP PROCEDURE IF EXISTS `drop_empty_tables_from` $$

CREATE PROCEDURE `drop_empty_tables_from`(IN schema_target VARCHAR(128))
BEGIN
    DECLARE table_list TEXT;
    DECLARE total      VARCHAR(11);

    SELECT
        GROUP_CONCAT(`TABLE_NAME`),
        COUNT(`TABLE_NAME`)
    INTO
        table_list,
        total
    FROM `information_schema`.`TABLES`
    WHERE
          `TABLE_SCHEMA` = schema_target
      AND `TABLE_ROWS`   = 0;

    IF table_list IS NOT NULL THEN
        SET @drop_tables = CONCAT("DROP TABLE ", table_list);

        PREPARE stmt FROM @drop_tables;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;

    SELECT total AS affected_tables;
END $$

DELIMITER ;

There may be problems with that GROUP_CONCAT when there are too many empty tables. It depends on the value of the group_concat_max_len system variable.

It's not possible to do it in one query because DROP TABLE cannot receive its arguments from a SELECT query.

InnoDB note

Thanks to James for his comments. It appears that the row count query won't return precise results in the case of InnoDB tables, so the above procedure is not guaranteed to work perfectly when there are InnoDB tables in that schema.

For InnoDB tables, the row count is only a rough estimate used in SQL optimization. (This is also true if the InnoDB table is partitioned.)

Source: http://dev.mysql.com/doc/refman/5.1/en/tables-table.html

Use pt-find from Percona Toolkit:

$ pt-find --dblike "mydatabase" --empty --exec-plus "DROP TABLE %s"

And a PHP version for completeness. It won't drop anything, just print for you the DROP statements:

<?php
$username="root";
$password="mypassword";
$database="mydatabase";
mysql_connect('localhost',$username,$password);
mysql_select_db($database) or die( "Unable to select database");

function drop_empty_tables(){
    $tables = mysql_query('SHOW TABLES');
    while($table = mysql_fetch_array($tables)){
        $table = $table[0];
        $records = mysql_query("SELECT * FROM $table");
        if(mysql_num_rows($records) == 0){
            // mysql_query("DROP TABLE $table");
            echo "DROP TABLE $table;\n";
        }
    }
}

drop_empty_tables();
?>