Generating Object DDL Scripts with Drop Statements

thatjeffsmith SQL Developer 5 Comments

Tell Others About This Story:

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!

Related Posts

Tell Others About This Story:

Comments 5

  1. 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

    1. thatjeffsmith Post
      Author
      1. 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.

      2. thatjeffsmith Post
        Author

        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.

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

Leave a Reply

Your email address will not be published. Required fields are marked *