/SQL/Drop all database tables oracle sql developer

Drop all database tables oracle sql developer

BEGIN
   FOR cur_rec IN (SELECT object_name, object_type
                   FROM user_objects
                   WHERE object_type IN
                             ('TABLE',
                              'VIEW',
                              'MATERIALIZED VIEW',
                              'PACKAGE',
                              'PROCEDURE',
                              'FUNCTION',
                              'SEQUENCE',
                              'SYNONYM',
                              'PACKAGE BODY'
                             ))
   LOOP
      BEGIN
         IF cur_rec.object_type = 'TABLE'
         THEN
            EXECUTE IMMEDIATE 'DROP '
                              || cur_rec.object_type
                              || ' "'
                              || cur_rec.object_name
                              || '" CASCADE CONSTRAINTS';
         ELSE
            EXECUTE IMMEDIATE 'DROP '
                              || cur_rec.object_type
                              || ' "'
                              || cur_rec.object_name
                              || '"';
         END IF;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line ('FAILED: DROP '
                                  || cur_rec.object_type
                                  || ' "'
                                  || cur_rec.object_name
                                  || '"'
                                 );
      END;
   END LOOP;
   FOR cur_rec IN (SELECT * 
                   FROM all_synonyms 
                   WHERE table_owner IN (SELECT USER FROM dual))
   LOOP
      BEGIN
         EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM ' || cur_rec.synonym_name;
      END;
   END LOOP;
END;
/

Source: stackoverflow.com

Similar codes

0 vote140 views1 answer
Get last run query sql server
SQL
0 vote180 views2 answers
Update all columns in one update
SQL
0 vote184 views1 answer
Object of class mysqli_result could not be converted to string
SQL
2 votes190 views1 answer
Mysql application.properties spring boot
SQL
18 votes177 views4 answers
Uninstall mysql ubuntu 18.04
SQL
1 vote234 views1 answer
Varchar plsql
SQL
0 vote226 views1 answer
Error code 1215 cannot add foreign key constraint
SQL
16 votes170 views2 answers
Add column to all tables after first column mysql
SQL
13 votes237 views6 answers
Mysql remove duplicates
SQL
0 vote200 views1 answer
Duplix print in Smartforms
SQL