Re: sql script delete all tables

>>>>> "teen" == teen <s4012051_at_student.uq.edu.au> writes:

>> OR,
>>
>> SELECT 'drop table '||tname||' from tab ; ' ;

 teen> Am I meant to replace ||tname|| with the table name? Or is  teen> that some kind of command?

>> Then copy and paste...

 teen> Ahh yes, that will kind of defeat the purpose of making a  teen> script :/

>>

 teen> I was thinking along the lines of something like DROP TABLE *  teen> WHERE TABLE_NAME IN (SELECT TABLE_NAME FROM USER_TABLES);

 teen> obviously i'm unsure if the syntax is correct.. i'm soon
 teen> installing oracle on this system so i'll be able to play around
 teen> a bit.

 teen> thanks for any hints I can get :P

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;
/

-- 
Tim Cross
The e-mail address on this message is FALSE (obviously!). My real e-mail is
to a company in Australia called rapttech and my login is tcross - if you 
really need to send mail, you should be able to work it out!