Delete all tables in Derby DB

Question

How do i delete all the tables in the schema on Apache Derby DB using JDBC?

Answer 1

I think most db providers don't allow DROP TABLE * (or similar).

I think the best way would be to SHOW TABLES and then go through each deleting in a loop via a resultset.

HTH.

Answer 2

Do a little method in java in which you execute a

"DROP TABLE [tablename]"

tablename is passed by parameter.

And another method in which you loop over a recordset formed by the query

"SELECT tablename FROM SYSTABLES"

calling the first method.

Derby latest documentation

Answer 3

JDBC allows you to solve your task in a database agnostic way:

1. Open the connection
2. Grab the DatabaseMetaData
3. Use it to list all tables in your database JavaDoc
4. Iterate over the resultset and fire the DROP TABLE for each table

Answer 4

1. you must generate schema and table name from Derby DB system catalog.
2. Order all tables by relation.
3. Generate java statement for drop all tables
4. Use autoCommit() method and set this method to false. for manual commit or rollback transactions when got errors.
5. Run you java process. Good Luck.

Answer 5

A simpler solution is to use JDBC to run "drop database foo" then "create database foo". However, this will cause all objects in the DB to be deleted (i.e. not just tables).

Answer 6

For actual code that does this, check CleanDatabaseTestSetup.java in the Derby test suite section of the Derby distribution: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/CleanDatabaseTestSetup.java?view=markup