How to drop all tables in database without dropping the database itself?

Question

I would like to delete all the tables from database, but not deleting the database itself. Is it possible ? I'm just looking for shorter way than removing the database and create it again. Thanks !

Answer 1

The shortest is to re-create database. but if you don't want to...

This is for MySQL/PHP. Not tested but something like that.

$mysqli = new mysqli("host", "my_user", "my_password", "database");
$mysqli->query('SET foreign_key_checks = 0');
if ($result = $mysqli->query("SHOW TABLES"))
{
    while($row = $result->fetch_array(MYSQLI_NUM))
    {
        $mysqli->query('DROP TABLE IF EXISTS '.$row[0]);
    }
}

$mysqli->query('SET foreign_key_checks = 1');
$mysqli->close();

Answer 2

There is no simple way to do this. Either you'll need to know what the tables are in advance:

//edit you can get this information using the query SHOW TABLE STATUS

$tables = array('users','otherdata');
foreach($tables as $table){
  db.execute("DROP TABLE "+$table);
}

or you can drop the database and re-create it empty (it's really not that much effort!):

db.execute('DROP DATABASE SITEDATA');
db.execute('CREATE DATABASE SITEDATA');

Answer 3

I needed to drop all tables except a couple from an inadvertent dump.

A PHP function to drop all tables except some (adapted from here), for anyone else who might need:

<?php
$mysqli = new mysqli( "localhost", "user", 'password', "database");
function drop_all_tables($exceptions_array, $conn) {
    $exceptions_string="('" ;
    foreach ($exceptions_array as $table) {
        $exceptions_string .=$table . "','";
    }
    $exceptions_string=rtrim($exceptions_string, ",'");
    $exceptions_string .="')" ;
    $sql="SELECT CONCAT('DROP TABLE ', TABLE_NAME, '; ')
         FROM information_schema.tables
         WHERE table_schema = DATABASE() AND table_name NOT IN $exceptions_string";
    $result=$ conn->query($sql);
    while($row = $result->fetch_array(MYSQLI_NUM)) {
        $conn->query($row[0]);
    }
}

//drop_all_tables(array("table1","table2","table3","table4"), $mysqli);
?>

Answer 4

You'd have to drop every table in the db separately, so dropping the database and recreating it will actually be the shortest route (and the fastest one for that matter).

Answer 5

When I had to do this in Oracle, I would write a select statement that would generate the drop table statements for me. Something to the effect of:

Select 'DROP TABLE ' || table_name || ';' from user_tables;

I could then pipe the output of the select statement to a file. After I ran this, I would have a file that would drop all my tables for me. It would look something like:

DROP TABLE TABLE1;

DROP TABLE TABLE2;

DROP TABLE TABLE3;

etc...

Not a mysql expert, but I would imagine it would have a similar facility to both select all tables for a schema, as well as direct output from a SQL statement to a file.

Answer 6

Use SHOW TABLE STATUS to get all tables in your database, then loop over result and drop them one by one.

Answer 7

There are some solutions here in comments: http://dev.mysql.com/doc/refman/5.1/en/drop-table.html

Answer 8

A procedural way to do this is as follows:

$query_disable_checks = 'SET foreign_key_checks = 0';

$query_result = mysqli_query($connect, $query_disable_checks);

// Get the first table
$show_query = 'Show tables';
$query_result = mysqli_query($connect, $show_query);
$row = mysqli_fetch_array($query_result);

while ($row) {
  $query = 'DROP TABLE IF EXISTS ' . $row[0];
  $query_result = mysqli_query($connect, $query);

  // Getting the next table
  $show_query = 'Show tables';
  $query_result = mysqli_query($connect, $show_query);
  $row = mysqli_fetch_array($query_result);
}

Here $connect is just the connection made with mysqli_connect();.

Answer 9

The single line query to drop all tables, as below:

$dbConnection = mysqli_connect("hostname", "username", "password", "database_name");
$dbConnection->query('SET foreign_key_checks = 0');

$qry_drop = "DROP TABLE IF EXISTS buildings, business, computer, education, fashion, feelings, food, health, industry, music, nature, people, places, religion, science, sports, transportation, travel";            
$dbConnection->query($qry_drop);

$mysqli->query('SET foreign_key_checks = 1');
$mysqli->close();

Answer 10

You can execute this. Just add more tables if I missed any

drop table wp_commentmeta;
drop table wp_comments;
drop table wp_links;
drop table wp_options;
drop table wp_postmeta;
drop table wp_posts;
drop table wp_term_relationships;
drop table wp_term_taxonomy;
drop table wp_termmeta;
drop table wp_terms;
drop table wp_usermeta;
drop table wp_users;