Home » SQL & PL/SQL » SQL & PL/SQL » How can i drop all tables in a database schema with delete message prompt ??
How can i drop all tables in a database schema with delete message prompt ?? [message #264439] Mon, 03 September 2007 06:00 Go to next message
anirban_hi
Messages: 3
Registered: September 2007
Location: INDIA
Junior Member
hi..

I want to drop all tables in a databse scheme,for that purpose I want to write a script which will ask deletion confirmation message before deletion of each table.

plz help me.

Anirban
Re: How can i drop all tables in a database schema with delete message prompt ?? [message #264479 is a reply to message #264439] Mon, 03 September 2007 08:38 Go to previous messageGo to next message
jheronimus
Messages: 11
Registered: March 2007
Location: The Netherlands, Groninge...
Junior Member
Suppose you have got 2 tables magweg and magweg1
You can write (or even generate) a script drop.sql just like the next example. Save this script as drop.sql on a disk.

set verify off
set pause off
set heading off
set pagesize 0

accept l_sure -
prompt "drop magweg. Are you sure? y/n :"
spool c:\temp\drop.sql
select case when 'y' = '&l_sure'
       then
          'drop table magweg;'
       end
from   dual
;
spool off
@c:\temp\drop.sql
undefine l_sure

accept l_sure -
prompt "drop magweg1. Are you sure? y/n :"
spool c:\temp\drop.sql
select case when 'y' = '&l_sure'
       then
          'drop table magweg1;'
       end
from   dual
;
spool off
@c:\temp\drop.sql
undefine l_sure


Next in sql you type:
sql>drop.sql


Kind regards
Jeroen de Jong
OSA it, The Netherlands

[Updated on: Mon, 03 September 2007 08:50]

Report message to a moderator

Re: How can i drop all tables in a database schema with delete message prompt ?? [message #264614 is a reply to message #264479] Mon, 03 September 2007 23:15 Go to previous messageGo to next message
anirban_hi
Messages: 3
Registered: September 2007
Location: INDIA
Junior Member
I need more generic script..which will prompt for arbitrary n tables in a database schma..and before deletion it will prpmpt for deletion confirmation message for each table. I want to delete from User_Objects entries...
u can modify these piece of code:

DECLARE

CURSOR cursor_del_table

IS

SELECT object_name

FROM user_objects

WHERE object_type = 'TABLE'

AND object_name <> 'REF_METER_IDS';



v_object_name user_objects.object_name%TYPE;

BEGIN

NULL;



OPEN cursor_del_table;



LOOP

FETCH cursor_del_table

INTO v_object_name;



DBMS_OUTPUT.put_line (v_object_name);

EXIT WHEN cursor_del_table%NOTFOUND;

END LOOP;



CLOSE cursor_del_table;

END;

/



thanks &regards

Anirban
Re: How can i drop all tables in a database schema with delete message prompt ?? [message #264616 is a reply to message #264439] Mon, 03 September 2007 23:21 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
When your only tool is a hammer, you treat all problems as nails.

IMO, SQL*Plus is the wrong tool for this task; plus PL/SQL is NOT designed to support an interactive application directly.
Re: How can i drop all tables in a database schema with delete message prompt ?? [message #264617 is a reply to message #264614] Mon, 03 September 2007 23:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It's not difficult to write a script that will generate a scrit like jheronimus's one looping on user_objects.

Regards
Michel
Re: How can i drop all tables in a database schema with delete message prompt ?? [message #264620 is a reply to message #264617] Mon, 03 September 2007 23:38 Go to previous messageGo to next message
anirban_hi
Messages: 3
Registered: September 2007
Location: INDIA
Junior Member
Can u send me the script using looping that delete users_object entries?

Anirban
Re: How can i drop all tables in a database schema with delete message prompt ?? [message #264621 is a reply to message #264439] Mon, 03 September 2007 23:41 Go to previous message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
>Can u send me the script using looping that delete users_object entries?

Sure. Can you send me a check for $10,000 USD?

How much are you getting paid to solve this problem?
How much are we getting paid to solve YOUR problem?
Previous Topic: Problems with Self_join
Next Topic: PL/SQL code of HMAC-MD5 algorithm
Goto Forum:
  


Current Time: Mon Dec 05 09:05:19 CST 2016

Total time taken to generate the page: 0.23126 seconds