MySQL - DROP TABLESPACE Statement

Table of content

Quiz

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.

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.

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;

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

Following is the syntax to do so −

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

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

Print Page