Delete all tables in Derby DB

Shimi Bandiel picture Shimi Bandiel · Oct 5, 2008 · Viewed 30.7k times · Source

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

Answer

Sym-Sym picture Sym-Sym · Apr 6, 2014

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.