Re: Reg. 'drop table' statement

From: Brian Peasland <dba_at_remove_spam.peasland.com>
Date: Mon, 18 Aug 2003 13:17:18 GMT
Message-ID: <3F40D1DE.FE2246E1@remove_spam.peasland.com>

SELECT 'DROP TABLE '||table_name||';'
FROM user_tables
WHERE table_name like '%TESTTABLE%';

Save the output as a script and run that script.

Or, do something like the following:

DECLARE
   table_nm USER_TABLES.TABLE_NAME%TYPE;    CURSOR c1 IS SELECT table_name FROM user_tables

                WHERE table_name like '%TESTTABLE%';

   drop_cmd VARCHAR2(100);
BEGIN
   OPEN c1;
   LOOP

      FETCH c1 INTO table_nm;
      EXIT WHEN c1%NOTFOUND;
      drop_cmd := 'DROP TABLE '||table_nm;
      EXEC IMMEDIATE drop_cmd;

   END LOOP;
   CLOSE c1;
END;
/

The above was off the top of my head so it may contain syntax errors.

HTH,
Brian

qazmlp wrote:
>
> I want to drop the table where table names ends with 'TESTTABLE'.
> I tried with this:
> drop table (select tname where tname like '%TESTTABLE');
>
> But, it does not work.
>
> Please help me to fix it.
>
> Thanks!

-- 
===================================================================

Brian Peasland
dba_at_remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
 the three"

Received on Mon Aug 18 2003 - 08:17:18 CDT