How to Drop all tables in a MySQL database

I’m doing some research in owncloud installation and every time i want to clear the database tables so that, i can go forward. Every time i used to drop the database and recreate it. Its very hard to do that. Then i thought to drop all the tables except database. For removing all tables from database use the below command.



Now, I’m going to check owncloud database tables, see below.

[email protected] [~]# mysql -u root -p
Enter password: *****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15045
Server version: 5.5.37-cll MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Navigating the corresponding database

mysql> use owncloud_cloud;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

Listing tables on corresponding database and its shows 33 tables is present in owncloud_cloud database.

mysql> show tables;
+------------------------------+
| Tables_in_owncloud_cloud     |
+------------------------------+
| oc_activity                  |
| oc_appconfig                 |
| oc_clndr_calendars           |
| oc_clndr_objects             |
| oc_clndr_repeat              |
| oc_clndr_share_calendar      |
| oc_clndr_share_event         |
| oc_contacts_addressbooks     |
| oc_contacts_cards            |
| oc_contacts_cards_properties |
| oc_documents_invite          |
| oc_documents_member          |
| oc_documents_op              |
| oc_documents_revisions       |
| oc_documents_session         |
| oc_file_map                  |
| oc_filecache                 |
| oc_files_trash               |
| oc_files_trashsize           |
| oc_files_versions            |
| oc_gallery_sharing           |
| oc_group_admin               |
| oc_group_user                |
| oc_groups                    |
| oc_jobs                      |
| oc_locks                     |
| oc_lucene_status             |
| oc_mimetypes                 |
| oc_permissions               |
| oc_pictures_images_cache     |
| oc_preferences               |
| oc_privatedata               |
| oc_properties                |
| oc_share                     |
| oc_storages                  |
| oc_users                     |
| oc_vcategory                 |
| oc_vcategory_to_object       |
+------------------------------+
38 rows in set (0.00 sec)

Quit from MySQL database server

mysql> exit
Bye

Here i’m going to run the single command to clear the particular database tables. You need to add your database name instead of owncloud_cloud.

[email protected] [~]# mysql -Nse 'show tables' owncloud_cloud | while read table; do mysql -e "drop table $table" owncloud_cloud; done

Command executed successfully and i want to check the database once again whether its dropped or not. Login to MySQL database again.

[email protected] [~]# mysql -u root -p
Enter password: *****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15106
Server version: 5.5.37-cll MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Navigating to corresponding database.

mysql> use owncloud_cloud;
Database changed

Listing database tables. Yes its successfully dropped all the tables from owncloud_cloud database.

mysql> show tables;
Empty set (0.00 sec)

Magesh Maruthamuthu

Love to play with all Linux distribution

You may also like...