CODEWITHSUNDEEP
phpsqlmysqldrop-table
Misha Moroshko
Misha Moroshko

Reputation: 171321

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

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 !

Upvotes: 23

Views: 40287

Answers (10)

R. Gurung
R. Gurung

Reputation: 1506

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();.

Upvotes: 1

Abdullah Aman
Abdullah Aman

Reputation: 1620

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;

Upvotes: 0

user9453341
user9453341

Reputation: 429

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();

Upvotes: -1

Honinbo Shusaku
Honinbo Shusaku

Reputation: 1511

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);
?>

Upvotes: 2

Vladislav Rastrusny
Vladislav Rastrusny

Reputation: 29965

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

Upvotes: 2

Tomasz Struczyński
Tomasz Struczyński

Reputation: 3303

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();

Upvotes: 44

fredley
fredley

Reputation: 33891

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');

Upvotes: 6

IcanDivideBy0
IcanDivideBy0

Reputation: 1675

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

Upvotes: 2

Brett McCann
Brett McCann

Reputation: 2519

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.

Upvotes: 2

wimvds
wimvds

Reputation: 12850

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).

Upvotes: 2

Related Questions