Is there a way for SQL Developer to generate a DROP statement when exporting the DDL for a table?

Yes.

This page in the preferences defines how the DDL is generated.
This page in the preferences defines how the DDL is generated.

The drop preference will be applied when generating the DDL for scripting purposes, but not for the table editor.

So to get the table CREATE DDL script with a DROP TABLE command, right-click on your table, and send the ‘Quick DDL’ to a worksheet, clipboard, or file.

There’s also a ‘Cascade Drops‘ preference, which is a bit more forceful when it comes to foreign key constraints that reference the object being dropped.

You'll see similar preferences when exporting objects via the Cart or Tools > Database Export
You’ll see similar preferences when exporting objects via the Cart or Tools > Database Export

You’ll notice on the preferences page you can also turn off things like storage – here’s the script again with storage disabled.

No storage clauses...
No storage clauses…

If you want even more control over the DDL being generated, export your object(s) to a data model and generate the DDL from there

Like, maybe you don’t like quoted object names in your DDL – like me 🙂

More options!
More options!
Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

5 Comments

  1. Salek Talangi Reply

    Hi Jeff,

    I did not find an option to DROP the generated sequence for the table, yet. Is it also there? So far, my workaround is to use [Table properties]->[Scripts]->[Before create].

    Best regards,
    Salek

    • Salek Talangi

      Of course, they aren’t. But they are (or “can be”) generated together WITH the table (also together with the needed before-insert trigger) if the table has an identity column. So they should also be removed when the table is removed.
      Reason for this: I want to “powercycle” my schema with the same script over and over again. For the trigger this does not matter because of “CREATE OR REPLACE”, which is not true for the sequence.

    • Yes, but I can generate anything with my table. You’ll need to include sequences and use the Database Export wizard, or piece meal your deployment script using the Cart if you want to deal with things like sequences.

    • Salek Talangi

      Ah, sorry. There is a misunderstanding. I was talking about SQL Developer Data Modeler, while you were talking about SQL Developer.

Write A Comment