MySQL - DROP TABLESPACE Statement

---

---

MySQL DROP TABLESPACE Statement

You can drop/delete an existing table using the DROP TABLESPACE Statement. This statement is supported by the NDB and InnoDB storage engines.

Syntax

Following is the syntax of the DROP TABLESPACE statement −

DROP TABLESPACE tablespace_name

Where, tablespace_name is the name of the table space you need to delete.

Example

Suppose we have created tables as shown below −

CREATE TABLESPACE sample_tablespace;

You can delete three of the above created table spaces using the DROP TABLESPACE statement as shown below −

DROP TABLESPACE sample_tablespace;

The ENGINE option

You can also drop the table pace by providing the engine name.

Syntax

Following is the syntax to do so −

DROP [UNDO] TABLESPACE tablespace_name [ENGINE [=] engine_name]

Example

Assume we have created another tablespace with name test as shown below −

CREATE TABLESPACE sample ADD DATAFILE 'sample.ibd' ENGINE = INNODB;

Following query deletes the above created table space −

DROP TABLESPACE sample;

Since we have deleted the sample tablespace, If you retrieve the schema files as shown below, you will get an empty set −

select * from INFORMATION_SCHEMA.FILES where TABLESPACE_NAME ='sample'\G