22

Possible Duplicate:
Truncate all tables in a MySQL database in one command?

I need to delete, or drop, all the tables in a MySQL database with single command without knowing the tablenames. Is this possible?

Community
  • 1
  • 1
deepa
  • 455
  • 3
  • 8
  • 17

2 Answers2

39
drop database YOUR_DATABASE;
/* this will delete all the tables for this database */

create database YOUR_DATABASE;
/* added back the database namespace */
ajreal
  • 46,720
  • 11
  • 89
  • 119
  • 4
    requires the specific right to drop it though – dvhh Jan 20 '11 at 04:58
  • for this question i got another solution instead of drop database. i got all the list of tables using show tables and drop the tables one by one. – deepa Feb 02 '11 at 07:26
  • 1
    alright, but that is not using single command :) – ajreal Feb 02 '11 at 07:30
  • 1
    phpadmin not allowed me to use drop, anything else. – Anirudha Gupta Jan 15 '13 at 13:04
  • 12
    For the longest time, I thought this would reset permissions for some reason! ;_; all the wasted minutes. – Aditya M P Aug 14 '13 at 06:51
  • It will reset your character set / collation for the database though, so be careful. – Rag Jan 02 '14 at 18:22
  • 1
    I do this by phpmyadmin (I check foreign key check false) .Because **You cannot always get permission to create database**. Espesially, in **shared hosting enviornment**. So in my view, what to do is to select all tables from phpmyadmin and drop it. Disclaimer: May this answer OP looking for. But not suggestible as a general solution to all. – Ajeeb.K.P Mar 27 '15 at 11:09
  • now working in phpmyadmin of my ISP – To Kra May 05 '15 at 11:58
  • I know this works but this is not a right answer :| – Ali Rn Nov 19 '20 at 06:56
2

Rather long, but try this command (after replacing the obvious things):

mysql --user=YOUR_USERNAME --password=YOUR_PASSWORD -BNe "show tables" YOUR_DBSCHEMA_NAME | tr '\n' ',' | sed -e 's/,$//' | awk '{print "SET FOREIGN_KEY_CHECKS = 0;DROP TABLE IF EXISTS " $1 ";SET FOREIGN_KEY_CHECKS = 1;"}' | mysql --user=YOUR_USERNAME --password=YOUR_PASSWORD YOUR_DBSCHEMA_NAME
Tim Post
  • 33,371
  • 15
  • 110
  • 174
Suthagaran
  • 51
  • 1
  • 1
  • Don't know why you got downvoted (I don't think that should be allowed without a comment!), but this is great and better than [other linked answers](https://stackoverflow.com/a/8912749/623519) as it deals with foreign key checks too. Thanks. – artfulrobot Sep 28 '20 at 15:52