Web Dev Blog

Today during the course of some work, I came across a situation where I had to delete all tables from a MySQL database. Normally when working locally or on a machine where I have full access, I execute the “DROP DATABASE my_data” and then “CREATE DATABASE my_data” statements to completely delete all the tables in it. This works perfectly in this situation, as these statements will also drop all tables, leaving you with a fresh new database.

However, the database account I was issued on this MySQL server did not have the permissions to drop and create a database. In MySQL there is no
“DROP ALL TABLES” statement - this doesn’t exist in MySQL. There were over 100 tables that I had to delete, making manually executing the drop table statment for each table both time consuming and error prone.

To resolve the issue I came up with the following PHP script that will do this operation for you when executed.

Requirements: PHP 4+ and the MySQL extension enabled.

Note:  If you are using views, this script will be unable to drop them along with the tables. If you don’t know what views are, don’t worry about this notice.

 
/* fill in your database name */
$database_name = "my_db";
 
/* connect to MySQL */
if (!$link = mysql_connect("db_host_name", "username", "pass")) {
  die("Could not connect: " . mysql_error());
}
 
/* query all tables */
$sql = "SHOW TABLES FROM $database_name";
if($result = mysql_query($sql)){
  /* add table name to array */
  while($row = mysql_fetch_row($result)){
    $found_tables[]=$row[0];
  }
}
else{
  die("Error, could not list tables. MySQL Error: " . mysql_error());
}
 
/* loop through and drop each table */
foreach($found_tables as $table_name){
  $sql = "DROP TABLE $database_name.$table_name";
  if($result = mysql_query($sql)){
    echo "Success - table $table_name deleted.";
  }
  else{
    echo "Error deleting $table_name. MySQL Error: " . mysql_error() . "";
  }
}

Remember not to leave this script sitting on your server longer than needed, as it is a serious security risk.