Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I've a file with the SQL statements to create tables, indexes, etc, and another file with data test to use in the test database. Then, I want that they can be removed.

How to remove table and indexes from a database? Is there a generic way to remove them in MySQL and PostgreSQL?

share|improve this question
up vote 3 down vote accepted

The generic way to "remove" a table is to drop it:


When you drop the table all indexes will also be dropped. If you need to drop them separately use

DROP INDEX foo_index;

For PostgreSQL the easiest way to get rid of all objects in a schema is to drop the schema:


Of course you could have found out that yourself by simply looking at the manual:

share|improve this answer

As far as I know there is no generic way to so except creating another file that contains series of appropriate DROP statements.

Another way which I believe is more appropriate for testing is to create a backup, run your tests, and then restore database from the backup. Some RDMS (not Mysql though, and I'm not sure about Postgres) let you create database snapshots and restore from them later which is much faster then backup-restore.

share|improve this answer
Good point about the test initialization. In that case using create database .. with template bar; will be a lot more efficient in Postgres than restoring a dump. – a_horse_with_no_name Oct 28 '12 at 13:24
@a_horse_with_no_name : Thanks, it's good to know about Postgres with template – a1ex07 Oct 28 '12 at 13:35

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.