14

I need to drop all the tables in a database without knowing their names beforehand. The typical procedure is to drop and then recreate the database but this is not an option. What is the best way to do it?

Angel Chiang
  • 431
  • 1
  • 4
  • 9

5 Answers5

19

There is a simpler bash one-liner using mysqldump (from Thingy Ma Jig Blog).

mysqldump -u[USERNAME] -p[PASSWORD] --add-drop-table --no-data [DATABASE] | grep ^DROP | mysql -u[USERNAME] -p[PASSWORD] [DATABASE]

If you're getting this error:

ERROR 1217 (23000) at line 1: Cannot delete or update a parent row: 
    a foreign key constraint fails

Try the following:

(echo 'SET foreign_key_checks = 0;'; 
 (mysqldump -u[USERNAME] -p[PASSWORD] --add-drop-table --no-data [DATABASE] | 
     grep ^DROP); 
 echo 'SET foreign_key_checks = 1;') | \
    mysql -u[USERNAME] -p[PASSWORD] -b [DATABASE]

Now it ignores the constraints.

Bart De Vos
  • 17,911
  • 6
  • 63
  • 82
Angel Chiang
  • 431
  • 1
  • 4
  • 9
4

You should use the information_schema tables to fetch the metadata about the database, and then drop the tables listed there.

Joril
  • 1,610
  • 1
  • 21
  • 28
3

You could try a quick shell script to do it too:

#!/bin/bash

IFS=$'\n'
for table in `mysql <databaseName> -N -e 'show tables'`; do
    echo mysql <databaseName> -e "drop table $table"
done

Remove echo after you've checked that it's going to do what you expect.

Jeff Snider
  • 3,272
  • 18
  • 17
  • By the way, why do you write '$' before IFS's value? Why doesn't it look like "IFS='\n'" ? – kolypto Nov 06 '09 at 15:13
  • @o_OTync Using `$''` causes bash to interpret backslashed sequences instead of taking them literally. `'\n'` would contain exactly \n, just like that, which `$IFS` would understand to be splitting on \ and n characters. `$'\n'` would contain a newline character, 0x0D. Take a look here for more info: http://www.gnu.org/software/bash/manual/bashref.html#ANSI\_002dC-Quoting. – Jeff Snider Nov 06 '09 at 15:41
  • I think this is simpler than using the INFORMATION_SCHEMA. – Angel Chiang Nov 10 '09 at 13:05
0

There was an entry on the Xaprb blog recently which covers this nicely.

It includes why using INFORMATION_SCHEMA isn't always a good thing and references a tool from those lovely people at Maatkit.

Try the following with mk-find:

mk-find '<database>' --printf 'DROP TABLE %D.%N'

If you're happy with the results then:

mk-find '<database>' --exec 'DROP TABLE %D.%N'
Dan Carley
  • 25,617
  • 5
  • 53
  • 70
-3

If you have filesystem access, then just rm -rf databasename/* :).

The Drop database statement does the same... Excerpt from the MySQL manual:

The DROP DATABASE statement removes from the given database directory those files and directories that MySQL itself may create during normal operation:

Jauzsika
  • 651
  • 1
  • 4
  • 10
  • 2
    Extremely bad idea for any non-trivial setup as MySQL is not expecting these files to suddenly disappear and could be in the middle of any number of operations, transactions, replication, locks... – bot403 Jan 05 '12 at 22:53
  • Would you consider revising your answer to include the proper commands and procedure then? – bot403 Jan 06 '12 at 19:00