Re: Re: sql script delete all tables

>Tim X <timx_at_spamto.devnul.com> wrote in message news:<87of43lptd.fsf_at_tiger.rapttech.com.au>...
>> Untested and off the top of my head - but it might give you the
>> idea. ASsuming oracle 8i or better
>>
>> set serveroutput on size 1000000
>> declare
>> cursor tab_list is
>> select table_name
>> from user_tables;
>> tname tab_list_at_ROWTYPE;
>> sql_str varchar2(256);
>> begin
>> for tname in tab_list loop
>> sql_str := 'drop table '||tname;
>> execute immediate sql_str;
>> end loop;
>> exception
>> when others then
>> dbms_output.put_line(SQLERRM);
>> end;
>> /
>
>That will almost work, but has 1 bug and some redundant variable
>declarations, redundant exception handler etc. This is enough:
>
>begin
> for trec in (select table_name from user_tables) loop
> execute immediate 'drop table '||trec.table_name;
> end loop;
>end;
>/
>

OMFG I LOVE YOU
You are the winner (I appended cascade stuff):

begin
for trec in (select table_name from user_tables) loop execute immediate 'drop table '||trec.table_name||' cascade constraint'; end loop;
end;
/

but yeah, it works sweet
propz Received on Mon Mar 24 2003 - 01:34:37 CST