How do i delete all the tables in the schema on Apache Derby DB using JDBC?
Thanks are due to the blog:
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';
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.