CODEWITHSUNDEEP
mysql
peace_love
peace_love

Reputation: 6471

How can I remove all tables from mySQL database with phpAdmin?

I need a simple command to remove all tables in my database. Couldn't find it nowhere. Something like

DELETE ALL TABLES of `db1231123`

Upvotes: 1

Views: 4624

Answers (1)

trincot
trincot

Reputation: 350272

How to reset a database in phpMyAdmin

  1. Log into your cPanel interface.
  2. From the main cPanel screen, look for the Databases category and then click on the phpMyAdmin tool.
  3. This opens the phpMyAdmin tool. Click on the Databases tab at the top of the page. This lists all the databases on the account. Find the database you want to work with and click on it.
  4. The next screen lists all the tables in the database. Scroll to the bottom and click on the Check all checkbox.
  5. Next to the Check All checkbox is a dropdown list for commands. Find and click on the Drop option.
  6. You will then be presented with a confirmation screen that displays the SQL Query phpMyAdmin will run. Click on the Yes button to confirm and run the query.
  7. After the query runs, the tables will be removed from the list. You should see a message at the top that says "No tables found in database". This means the database is empty.

NB: If you recreate the same database objects over and over again, you might want to save the script that is generated in step 6. Then you actually have the SQL to execute the next time you want to remove all tables again.

Alternative

You could also generate the necessary DROP TABLE statements with this statement:

SELECT CONCAT('DROP TABLE IF EXISTS ', table_name, ';')
FROM   information_schema.tables
WHERE  table_schema = 'your-db-name';

Take the output of that query, and put the following SET FOREIGN_KEY_CHECKS statements around it:

SET FOREIGN_KEY_CHECKS = 0;
-- here come the generated statements, for example:
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS companies;
-- ... etc.
--
SET FOREIGN_KEY_CHECKS = 1;

Upvotes: 4

Related Questions