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

Thanks are due to the blog:

Step 1:

Run the SQL statement, but don't forget to replace the schema name 'APP' with your your schema name in the 2 occurrences below:

SELECT
'ALTER TABLE '||S.SCHEMANAME||'.'||T.TABLENAME||' DROP CONSTRAINT '||C.CONSTRAINTNAME||';'
FROM
    SYS.SYSCONSTRAINTS C,
    SYS.SYSSCHEMAS S,
    SYS.SYSTABLES T
WHERE
    C.SCHEMAID = S.SCHEMAID
AND
    C.TABLEID = T.TABLEID
AND
S.SCHEMANAME = 'APP'
UNION
SELECT 'DROP TABLE ' || schemaname ||'.' || tablename || ';'
FROM SYS.SYSTABLES
INNER JOIN SYS.SYSSCHEMAS ON SYS.SYSTABLES.SCHEMAID = SYS.SYSSCHEMAS.SCHEMAID
where schemaname='APP';

Step 2:

The result of the above execution is a set of SQL statements, copy them to the SQL editor, execute them, then the constraints and the tables are dropped.

Answer 2

For actual code that does this, check CleanDatabaseTestSetup.java in the Derby test suite section of the Derby distribution.

Answer 3

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 record set formed by the query

SELECT tablename FROM SYSTABLES

calling the first method.

Derby latest documentation

Answer 4

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 5

If you're working from the command prompt rather than through JDBC, this should get you started.

SELECT 'DROP TABLE ' || schemaname ||'.' || tablename || ';'
FROM SYS.SYSTABLES
INNER JOIN SYS.SYSSCHEMAS ON SYS.SYSTABLES.SCHEMAID = SYS.SYSSCHEMAS.SCHEMAID
;

Answer 6

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 7

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 8

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 9

A simple solution is to do right click -> disconnect then delete the folder containing your database and reconnect it.

Answer 10

Download Squirrel SQL from http://squirrel-sql.sourceforge.net/

Connect to the database.

Expand the TABLE node.

Select the tables that you want to drop.

Right click and select -> Scripts -> Drop table scripts

Run the generated queries

You can even select delete records to empty the selected tables.

Answer 11

For those wanting to delete all schemas programmatically without having to manually copy-paste SQL each time, here's code lifted from org.apache.derbyTesting.junit.CleanDatabaseTestSetup and org.apache.derbyTesting.junit.JDBC. You just call dropAllSchemas(connection);

public static void dropAllSchemas(Connection conn) throws SQLException {
    DatabaseMetaData dmd = conn.getMetaData();
    SQLException sqle = null;
    // Loop a number of arbitary times to catch cases
    // where objects are dependent on objects in
    // different schemas.
    for (int count = 0; count < 5; count++) {
        // Fetch all the user schemas into a list
        List<String> schemas = new ArrayList<String>();
        ResultSet rs = dmd.getSchemas();
        while (rs.next()) {
            String schema = rs.getString("TABLE_SCHEM");
            if (schema.startsWith("SYS"))
                continue;
            if (schema.equals("SQLJ"))
                continue;
            if (schema.equals("NULLID"))
                continue;
            schemas.add(schema);
        }
        rs.close();
        // DROP all the user schemas.
        sqle = null;
        for (String schema : schemas) {
            try {
                dropSchema(dmd, schema);
            } catch (SQLException e) {
                sqle = e;
            }
        }
        // No errors means all the schemas we wanted to
        // drop were dropped, so nothing more to do.
        if (sqle == null)
            return;
    }
    throw sqle;
}


/**
 * Constant to pass to DatabaseMetaData.getTables() to fetch
 * just tables.
 */
public static final String[] GET_TABLES_TABLE = new String[] {"TABLE"};
/**
 * Constant to pass to DatabaseMetaData.getTables() to fetch
 * just views.
 */
public static final String[] GET_TABLES_VIEW = new String[] {"VIEW"};
/**
 * Constant to pass to DatabaseMetaData.getTables() to fetch
 * just synonyms.
 */
public static final String[] GET_TABLES_SYNONYM =
    new String[] {"SYNONYM"};
/**
 * Drop a database schema by dropping all objects in it
 * and then executing DROP SCHEMA. If the schema is
 * APP it is cleaned but DROP SCHEMA is not executed.
 * 
 * TODO: Handle dependencies by looping in some intelligent
 * way until everything can be dropped.
 * 

 * 
 * @param dmd DatabaseMetaData object for database
 * @param schema Name of the schema
 * @throws SQLException database error
 */
public static void dropSchema(DatabaseMetaData dmd, String schema) throws SQLException{     
    Connection conn = dmd.getConnection();
    Statement s = dmd.getConnection().createStatement();

    // Triggers
    PreparedStatement pstr = conn.prepareStatement(
            "SELECT TRIGGERNAME FROM SYS.SYSSCHEMAS S, SYS.SYSTRIGGERS T "
            + "WHERE S.SCHEMAID = T.SCHEMAID AND SCHEMANAME = ?");
    pstr.setString(1, schema);
    ResultSet trrs = pstr.executeQuery();
    while (trrs.next()) {
        String trigger = trrs.getString(1);
        s.execute("DROP TRIGGER " + escape(schema, trigger));
    }
    trrs.close();
    pstr.close();

    // Functions - not supported by JDBC meta data until JDBC 4
    // Need to use the CHAR() function on A.ALIASTYPE
    // so that the compare will work in any schema.
    PreparedStatement psf = conn.prepareStatement(
            "SELECT ALIAS FROM SYS.SYSALIASES A, SYS.SYSSCHEMAS S" +
            " WHERE A.SCHEMAID = S.SCHEMAID " +
            " AND CHAR(A.ALIASTYPE) = ? " +
            " AND S.SCHEMANAME = ?");
    psf.setString(1, "F" );
    psf.setString(2, schema);
    ResultSet rs = psf.executeQuery();
    dropUsingDMD(s, rs, schema, "ALIAS", "FUNCTION");        

    // Procedures
    rs = dmd.getProcedures((String) null,
            schema, (String) null);
    
    dropUsingDMD(s, rs, schema, "PROCEDURE_NAME", "PROCEDURE");
    
    // Views
    rs = dmd.getTables((String) null, schema, (String) null,
            GET_TABLES_VIEW);
    
    dropUsingDMD(s, rs, schema, "TABLE_NAME", "VIEW");
    
    // Tables
    rs = dmd.getTables((String) null, schema, (String) null,
            GET_TABLES_TABLE);
    
    dropUsingDMD(s, rs, schema, "TABLE_NAME", "TABLE");
    
    // At this point there may be tables left due to
    // foreign key constraints leading to a dependency loop.
    // Drop any constraints that remain and then drop the tables.
    // If there are no tables then this should be a quick no-op.
    ResultSet table_rs = dmd.getTables((String) null, schema, (String) null,
            GET_TABLES_TABLE);

    while (table_rs.next()) {
        String tablename = table_rs.getString("TABLE_NAME");
        rs = dmd.getExportedKeys((String) null, schema, tablename);
        while (rs.next()) {
            short keyPosition = rs.getShort("KEY_SEQ");
            if (keyPosition != 1)
                continue;
            String fkName = rs.getString("FK_NAME");
            // No name, probably can't happen but couldn't drop it anyway.
            if (fkName == null)
                continue;
            String fkSchema = rs.getString("FKTABLE_SCHEM");
            String fkTable = rs.getString("FKTABLE_NAME");

            String ddl = "ALTER TABLE " +
                escape(fkSchema, fkTable) +
                " DROP FOREIGN KEY " +
                escape(fkName);
            s.executeUpdate(ddl);
        }
        rs.close();
    }
    table_rs.close();
    conn.commit();
            
    // Tables (again)
    rs = dmd.getTables((String) null, schema, (String) null,
            GET_TABLES_TABLE);        
    dropUsingDMD(s, rs, schema, "TABLE_NAME", "TABLE");

    // drop UDTs
    psf.setString(1, "A" );
    psf.setString(2, schema);
    rs = psf.executeQuery();
    dropUsingDMD(s, rs, schema, "ALIAS", "TYPE");        

    // drop aggregates
    psf.setString(1, "G" );
    psf.setString(2, schema);
    rs = psf.executeQuery();
    dropUsingDMD(s, rs, schema, "ALIAS", "DERBY AGGREGATE");        
    psf.close();

    // Synonyms - need work around for DERBY-1790 where
    // passing a table type of SYNONYM fails.
    rs = dmd.getTables((String) null, schema, (String) null,
            GET_TABLES_SYNONYM);
    
    dropUsingDMD(s, rs, schema, "TABLE_NAME", "SYNONYM");
            
    // sequences
    if ( sysSequencesExists( conn ) )
    {
        psf = conn.prepareStatement
            (
             "SELECT SEQUENCENAME FROM SYS.SYSSEQUENCES A, SYS.SYSSCHEMAS S" +
             " WHERE A.SCHEMAID = S.SCHEMAID " +
             " AND S.SCHEMANAME = ?");
        psf.setString(1, schema);
        rs = psf.executeQuery();
        dropUsingDMD(s, rs, schema, "SEQUENCENAME", "SEQUENCE");
        psf.close();
    }

    // Finally drop the schema if it is not APP
    if (!schema.equals("APP")) {
        s.executeUpdate("DROP SCHEMA " + escape(schema) + " RESTRICT");
    }
    conn.commit();
    s.close();
}

    /**
 * Return true if the SYSSEQUENCES table exists.
 */
private static boolean sysSequencesExists( Connection conn ) throws SQLException
{
    PreparedStatement ps = null;
    ResultSet rs =  null;
    try {
        ps = conn.prepareStatement
            (
             "select count(*) from sys.systables t, sys.sysschemas s\n" +
             "where t.schemaid = s.schemaid\n" +
             "and ( cast(s.schemaname as varchar(128)))= 'SYS'\n" +
             "and ( cast(t.tablename as varchar(128))) = 'SYSSEQUENCES'" );
        rs = ps.executeQuery();
        rs.next();
        return ( rs.getInt( 1 ) > 0 );
    }
    finally
    {
        if ( rs != null ) { rs.close(); }
        if ( ps != null ) { ps.close(); }
    }
}

/**
 * Escape a non-qualified name so that it is suitable
 * for use in a SQL query executed by JDBC.
 */
public static String escape(String name)
{
    StringBuffer buffer = new StringBuffer(name.length() + 2);
    buffer.append('"');
    for (int i = 0; i < name.length(); i++) {
        char c = name.charAt(i);
        // escape double quote characters with an extra double quote
        if (c == '"') buffer.append('"');
        buffer.append(c);
    }
    buffer.append('"');
    return buffer.toString();
}   

/**
 * Escape a schema-qualified name so that it is suitable
 * for use in a SQL query executed by JDBC.
 */
public static String escape(String schema, String name)
{
    return escape(schema) + "." + escape(name);
}


/**
 * DROP a set of objects based upon a ResultSet from a
 * DatabaseMetaData call.
 * 
 * TODO: Handle errors to ensure all objects are dropped,
 * probably requires interaction with its caller.
 * 
 * @param s Statement object used to execute the DROP commands.
 * @param rs DatabaseMetaData ResultSet
 * @param schema Schema the objects are contained in
 * @param mdColumn The column name used to extract the object's
 * name from rs
 * @param dropType The keyword to use after DROP in the SQL statement
 * @throws SQLException database errors.
 */
private static void dropUsingDMD(
        Statement s, ResultSet rs, String schema,
        String mdColumn,
        String dropType) throws SQLException
{
    String dropLeadIn = "DROP " + dropType + " ";
    
    // First collect the set of DROP SQL statements.
    ArrayList<String> ddl = new ArrayList<String>();
    while (rs.next())
    {
        String objectName = rs.getString(mdColumn);
        String raw = dropLeadIn + escape(schema, objectName);
        if (
            "TYPE".equals( dropType )  ||
            "SEQUENCE".equals( dropType ) ||
            "DERBY AGGREGATE".equals( dropType )
            )
        { raw = raw + " restrict "; }
        ddl.add( raw );
    }
    rs.close();
    if (ddl.isEmpty())
        return;
            
    // Execute them as a complete batch, hoping they will all succeed.
    s.clearBatch();
    int batchCount = 0;
    for (Iterator i = ddl.iterator(); i.hasNext(); )
    {
        Object sql = i.next();
        if (sql != null) {
            s.addBatch(sql.toString());
            batchCount++;
        }
    }

    int[] results;
    boolean hadError;
    try {
        results = s.executeBatch();
        //Assert.assertNotNull(results);
        //Assert.assertEquals("Incorrect result length from executeBatch", batchCount, results.length);
        hadError = false;
    } catch (BatchUpdateException batchException) {
        results = batchException.getUpdateCounts();
        //Assert.assertNotNull(results);
        //Assert.assertTrue("Too many results in BatchUpdateException", results.length <= batchCount);
        hadError = true;
    }
    
    // Remove any statements from the list that succeeded.
    boolean didDrop = false;
    for (int i = 0; i < results.length; i++)
    {
        int result = results[i];
        if (result == Statement.EXECUTE_FAILED)
            hadError = true;
        else if (result == Statement.SUCCESS_NO_INFO || result >= 0) {
            didDrop = true;
            ddl.set(i, null);
        }
        //else
            //Assert.fail("Negative executeBatch status");
    }
    s.clearBatch();
    if (didDrop) {
        // Commit any work we did do.
        s.getConnection().commit();
    }

    // If we had failures drop them as individual statements
    // until there are none left or none succeed. We need to
    // do this because the batch processing stops at the first
    // error. This copes with the simple case where there
    // are objects of the same type that depend on each other
    // and a different drop order will allow all or most
    // to be dropped.
    if (hadError) {
        do {
            hadError = false;
            didDrop = false;
            for (ListIterator<String> i = ddl.listIterator(); i.hasNext();) {
                String sql = i.next();
                if (sql != null) {
                    try {
                        s.executeUpdate(sql);
                        i.set(null);
                        didDrop = true;
                    } catch (SQLException e) {
                        hadError = true;
                    }
                }
            }
            if (didDrop)
                s.getConnection().commit();
        } while (hadError && didDrop);
    }
}

PS: This code came in handy for when I migrated my database from H2 that does support DROP ALL OBJECTS, to Apache Derby which does not (headache). The only reason I migrated away from H2 is that it's a fully in-memory database and was getting too big for my server's RAM, so I decided to try Apache Derby. H2 is far easier and more user-friendly than Derby, I highly recommend it. I'm sad that I can't afford the RAM to keep using H2. By the way, for those affected by Derby's lack of LIMIT or UPSERT, see this post about substituting FETCH NEXT instead of LIMIT and this one about correctly using MERGE INTO.