Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003
    Posts
    22

    Unanswered: Delete Triggers/Procedures...

    What's the SQL statement that will allow me to delete all the triggers and procedures that I have created. (I don't remember the names of the procedures and triggers, but would like to clear everything). Thanks.

  2. #2
    Join Date
    May 2003
    Posts
    87

    Re: Delete Triggers/Procedures...

    If you can connect to the schema which created those triggers & procedures, you can get the names by querying the user_objects views.

    select object_type, object_name
    from user_objects
    where object_type like 'PROC%' or object_type like 'TRIG%';

    And to delete all the above, issue these sqls:

    Code:
    sql> set feedback off
    sql> set termout off
    sql> spool c:\temp\droptrigproc.sql
    sql> select 'drop '||object_type||' '||object_name
       2> from user_objects
      3> where object_type like 'PROC%' or object_type like 'TRIG%';
    sql> spool off
    sql> @c:\temp\droptrigproc.sql
    Before doing the above, make sure you are NOT deleting other required objects !

    Or the fastest way to cleanup would be to drop the schema/user and recreate it.

    Originally posted by VB_Oracle
    What's the SQL statement that will allow me to delete all the triggers and procedures that I have created. (I don't remember the names of the procedures and triggers, but would like to clear everything). Thanks.

Posting Permissions

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