viewschemahsqldbdrophypersql

How to drop all views in cascade manner from a schema in HSQL database

I want to drop all views available in particular schema of the HSQL database and recreate them. So how to drop all the view present in particular schema of HSQL DB?

Tried this:

DECLARE view_name VARCHAR(256);
DECLARE done BOOLEAN DEFAULT FALSE;

DECLARE cur CURSOR FOR 
    SELECT TABLE_NAME
    FROM INFORMATION_SCHEMA.VIEWS
    WHERE TABLE_SCHEMA = 'your_schema_name';

OPEN cur;
read_loop: LOOP
    FETCH cur INTO view_name;
    SET @drop_stmt = 'DROP VIEW IF EXISTS ' || view_name;
    EXECUTE IMMEDIATE @drop_stmt;
END LOOP;
CLOSE cur;
Solution

  • HSQLD does not allow DROP statements in stored procedures. You need to write your code in Java and execute DROP VIEW anyview IF EXISTS CASCADE for each view. Note you need the keyword CASCADE as a view that references other views cannot be dropped without this keyword.