Steps to Drop all tables in MySQL database

MySQL:

MySQL is the popular open source database with relational database management system. It is used by web-based application and its performance is high, reliable and it is very easy to use. Table contains set of elements with rows and columns. Creating the table is same as creating a database. Table name, field name and field description are needed for creating the table.

Code:

CREATE TABLE table_name (column_name column_type);

The above is the syntax for creating the table. Primary key is a column which identifies each row in a table. It contains unique values and it should not be NULL. A table has only one primary key. Syntax for primary key is

Code:

CONSTRAINT [constraint_name] PRIMARY KEY (column1, column2, ... column_n)

Example:

Foreign key matches the fields in another table. Primary key is set in the parent table and the foreign key is set in the child table. The column in the parent table is always referred by the columns in the child table. Primary key in one table will be referred as a foreign key in another table. Syntax for foreign key is

MySQL drop all tables:

Table has to be deleted very carefully because the data will be lost and it cannot be retrieved once a table is deleted. For deleting a table DROP TABLE command is used. The syntax is

Code:

DROP TABLE table_name ;

Full syntax is

Code:

DROP [ TEMPORARY ] TABLE [ IF EXISTS ]  table_name1, table_name2, ... [ RESTRICT | CASCADE ];

In the above syntax TEMPORARY are optional and only temporary tables will be dropped or deleted. Table_name is the name of the table that has to be dropped from the database, table_name1 and table_name2 specifies the name of the table. At the same time, many table can be dropped from the database. DROP TABLE delete the all specified table in the database and will show a NOTE or error message if the table is not available in the datable. IF EXISTS is optional and the DROP statement will not show error if the table doesn’t exist. When IF EXISTS is used a NOTE will be generated by MySQL. RESTRICT and CASCADE are optional and does not have any effect in the tables, they are used only to port the table to different database.

Example:

Code:

DROP TABLE customers;

The table named customers will be deleted.

Code:

DROP TABLE customers, suppliers;

The tables named customers and suppliers will be deleted from the database.

Code:

DROP TABLE IF EXISTS customers, suppliers;

Both the tables are deleted and error will not be shown if the table does not have any data.

Code:

DROP TABLE IF EXISTS customers, package;

Here table named package does not exist. A NOTE will be generated by MySQL using SHOW WARNING statement.

Code:

DROP TEMPORARY TABLE IF EXISTS customers;

The above table only delete the temporary table customer. IF there is a permanent table with the name customer it will not be deleted.

Dropping Tables with LIKE:

If there are many tables starting with same prefix and all the tables can be deleted using LIKE statement. Syntax for LIKE is

Code:

DROP TABLE LIKE '%pattern%'

LIKE is used to delete the tables based on matching the patterns. One can save the time using LIKE.

Steps to Drop all tables in MySQL database

More resources from bhawanisingh

Share this resource