Sql Remove All Tables From Database

There may come a time when you need to drop all tables in a MySQL database.

In this article, you'll learn how to do that easily, both using an SQL command an inside an IDE such as MySQL Workbench.

Let's see how we can do that.

Why Drop All Tables?

Removing all of the tables in the database will remove all of the data in the tables, giving you what looks like an empty database.

But why would you do this? Why not just drop the database?

To keep user privileges and other database objects such as views and procedures.

You can drop the database, and recreate it, but you'll lose the users, their permissions, and any other objects you have created such as views and stored procedures.

Dropping the tables is an easy thing to do if you want to reset your data.

So how do we do this?

Method 1: MySQL Drop All Tables with SQL

There is no "mysql drop all tables" command, but there is an easy way to generate it.

This process involves:

1. Selecting a list of tables from the data dictionary, and combining this with some text to generate a set of Drop Table statements.
2. Copying this list to an SQL window
3. Set the foreign key checks off and then on again.

So how do we do this?

Step 1: Generate a List of Drop Table Statements For All Tables

You can get a list of all tables in your MySQL database by running this query:

                SELECT table_name FROM information_schema.tables WHERE table_schema = 'database_name';              

Replace the word "database_name" with the name of your database.

Your result may look like this:

table_name

customer

employee

orders

product

But, to make this easier, we can add the command DROP TABLE IF EXISTS to the start of this output.

                SELECT CONCAT('DROP TABLE IF EXISTS `', table_name, '`;') FROM information_schema.tables WHERE table_schema = 'database_name';              

The backticks are used to ensure table names with special characters don't mess up the results.

The results are:

table_name

'DROP TABLE IF EXISTS `customer`;'

'DROP TABLE IF EXISTS `customer`;'

'DROP TABLE IF EXISTS `orders`;'

'DROP TABLE IF EXISTS `product`;'

Step 2: Copy and Paste The Results Into a New Window

Now we have a list of tables with the Drop Table command. Copy these into a new SQL editor window.

Your window should look something like this:

                DROP TABLE IF EXISTS `customer`; DROP TABLE IF EXISTS `employee`; DROP TABLE IF EXISTS `orders`; DROP TABLE IF EXISTS `product`;              

If you're using MySQL Workbench, for example, it would look like this:

You may need to right-click on your results and select "Copy Row (unquoted)" so you don't get quotes surrounding each line.

Step 3: Disable and Enable Foreign Key Checks

If you have foreign keys in your database, then you may encounter errors if you drop a table that is related to another table using foreign keys.

One way to get around this is to rearrange your SQL statements to drop the tables in a suitable order.

But that takes too long. A quicker way to do this is to disable the foreign key checks when these statements are run, so you can drop the tables and avoid the error.

Add this line above all of your Drop Table statements to disable the foreign key checks:

                SET FOREIGN_KEY_CHECKS = 0;              

Then, add this line at the end of your script to enable them:

                SET FOREIGN_KEY_CHECKS = 1;              

Step 4: Run The Script

Your script should now look something like this:

                SET FOREIGN_KEY_CHECKS = 0; DROP TABLE IF EXISTS `customer`; DROP TABLE IF EXISTS `employee`; DROP TABLE IF EXISTS `orders`; DROP TABLE IF EXISTS `product`; SET FOREIGN_KEY_CHECKS = 1;              

Run the script on your database, and all of the tables should be removed.

It's as easy as that!

Method 2: Drop All Tables In One Script

An answer on StackOverflow here has also provided a method for dropping all MySQL tables without the manual step of copying and pasting the Drop Table statements:

                SET FOREIGN_KEY_CHECKS = 0; SET GROUP_CONCAT_MAX_LEN=32768; SET @tables = NULL; SELECT GROUP_CONCAT('`', table_name, '`') INTO @tables   FROM information_schema.tables   WHERE table_schema = (SELECT DATABASE()); SELECT IFNULL(@tables,'dummy') INTO @tables; SET @tables = CONCAT('DROP TABLE IF EXISTS ', @tables); PREPARE stmt FROM @tables; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET FOREIGN_KEY_CHECKS = 1;              

This code will:

• Set some parameters, such as the maximum number of characters for group concat, and a variable called tables.
• The group concat function will concatenate rows into a single value, which is essentially what the earlier script was doing, but it's stored in a variable.
• A record called "dummy" is added in case there are no tables in the database.
• The Concat function is used to concatenate the table names with the Drop Table command.
• The variable is then turned into a statement which is then executed.

You can save this script in an SQL file for you and your team to run in the future.

Method 3: Drop All Tables in MySQL Workbench

If you are using an SQL IDE, there's usually a way you can delete tables in bulk.

In MySQL Workbench, you can drop all tables pretty easily as well.

Select all of the tables in your database in the Schema Browser clicking on the first table, holding Shift, and clicking on the last table.

Right-click on the selected tables and select "Drop (n) Tables…"

Click on Review SQL, as we will need to add in the foreign key check disable and enable.

Copy the SQL from the window that appears.

Paste it into a new SQL editor. Add the foreign key disable and enable commands, and run the query.

Conclusion

Dropping all tables in MySQL is fairly easy, as you can generate a statement from the information schema or data dictionary. There is no MySQL drop all tables statement, but this method is still pretty easy.

Sql Remove All Tables From Database

Source: https://www.databasestar.com/mysql-drop-all-tables/

Share this post