|
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   |
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   |
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 ®ards
Anirban
|
|
|
|
|
|
|