Results 1 to 2 of 2

Thread: Deleting tables

  1. #1
    Join Date
    Oct 2002
    Location
    chennai
    Posts
    30

    Question Unanswered: Deleting tables

    Hi all,

    Can any one tell me the query which can delete the tables which start with the same name. I mean, if T1, T2, T3, T4, T5.......Tn are the table names. How to delete all these tables with a single query.
    Thanks,
    sekhar rao k

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Deleting tables

    You can't do it in a single SQL statement, but you can do it with PL/SQL and dynamic SQL:

    Code:
    BEGIN
      FOR r IN (SELECT table_name FROM user_tables WHERE table_name LIKE 'T%')
      LOOP
        EXECUTE IMMEDIATE 'DELETE FROM '||r.table_name;
      END LOOP;
    END;
    /
    But you'd better be careful you don't catch any tables you didn't mean to! Run this first to see what tables it will delete:

    SELECT table_name FROM user_tables WHERE table_name LIKE 'T%';

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •