MySQL: dropping all tables of a database with ease

So there is a need to delete all tables from database?

Possibilities:

  1. Drop database and recreate, what if you don’t want to drop database, could be many reasons.
  2. Fire drop table [tbl_name ...] command on MySQL prompt but then who is going to type all tables names.

Solution:
Run following command from command line

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

Explained:

  1. First one takes dump of all tables of database without any data along with drop table command.
  2. Second part fetches lines starting with Drop from the output of first command.
  3. Third command executes queries fetched by second command which are only drop table commands.

Leave a Reply

Your email address will not be published. Please enter your name, email and a comment.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">