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

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 2

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 3

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 4

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

Answer 5

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 6

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