I just want to drop all table that start with "T%".
The db is Netezza.
Does anyone know the sql to do this?
With the catalog views and
execute immediate it is fairly simple to write this in nzplsql. Be careful though,
call drop_like('%') will destroy a database pretty fast.
create or replace procedure drop_like(varchar(128)) returns boolean language nzplsql as begin_proc declare obj record; expr alias for $1; begin for obj in select * from ( select 'TABLE' kind, tablename name from _v_table where tablename like upper(expr) union all select 'VIEW' kind, viewname name from _v_view where viewname like upper(expr) union all select 'SYNONYM' kind, synonym_name name from _v_synonym where synonym_name like upper(expr) union all select 'PROCEDURE' kind, proceduresignature name from _v_procedure where "PROCEDURE" like upper(expr) ) x loop execute immediate 'DROP '||obj.kind||' '||obj.name; end loop; end; end_proc;