Home » SQL & PL/SQL » SQL & PL/SQL » DROP ALL THE TABLES WITH A SINGLE QUERY
icon5.gif  DROP ALL THE TABLES WITH A SINGLE QUERY [message #210118] Tue, 19 December 2006 08:17 Go to next message
prasanna_anbu
Messages: 34
Registered: November 2006
Location: india
Member
In my schema i have some tables ending with _RET i want to drop those tables using sigle query is there any option
please help me on this with example
Re: DROP ALL THE TABLES WITH A SINGLE QUERY [message #210119 is a reply to message #210118] Tue, 19 December 2006 08:26 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

No you cant

An option would be to write a procedure to do it for you.
Re: DROP ALL THE TABLES WITH A SINGLE QUERY [message #210121 is a reply to message #210119] Tue, 19 December 2006 08:31 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You could write a little pl/sql block to do it:
BEGIN
  FOR rec IN (SELECT table_name FROM user_tables WHERE table_name LIKE '%RET') LOOP
    execute immediate 'DROP TABLE '||rec.table_name;
  END LOOP;
END;
Re: DROP ALL THE TABLES WITH A SINGLE QUERY [message #210252 is a reply to message #210121] Wed, 20 December 2006 00:54 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Another option would be an SQL*Plus script: let the script generate the drop commands and run those:
CREATE TABLE mhe_foo(id number)
/
CREATE TABLE mhe_blah(col1 varchar2(20))
/

SET TERMOUT OFF
SET VERIFY  OFF
SET PAGES   2000
SET HEADING OFF
SPOOL  droptables.sql

SELECT 'DROP TABLE '||table_name||';'
FROM   user_tables
WHERE  table_name LIKE 'MHE%'
/

SPOOL OFF
SET HEADING ON
SET PAGES   14
SET VERIFY  ON
SET TERMOUT ON

@@droptables.sql

SELECT table_name 
FROM   user_tables
WHERE  table_name
LIKE   'MHE%'
/


The run:
SQL> @orafaq

Table created.


Table created.


Table dropped.


Table dropped.


no rows selected


The content of spoolfile 'droptables.sql':
DROP TABLE MHE_BLAH;                                                            
DROP TABLE MHE_FOO;                                                             



MHE
Previous Topic: Date conversion with NCLOB (very urgent)
Next Topic: DBA users
Goto Forum:
  


Current Time: Thu Dec 08 00:42:08 CST 2016

Total time taken to generate the page: 0.10405 seconds