Jude Jude - 3 years ago 80
SQL Question

Drop multiple tables in Netezza

I just want to drop all table that start with "T%".

The db is Netezza.

Does anyone know the sql to do this?

Regards,

Answer Source

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;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download