How to Generate DDL Scripts for All Tables in SQL Developer

In this tutorial, we will list all the steps to generate a DDL script for all tables using SQL Developer.

Before starting exporting the DDL script for all tables, we are going to use the Data Modeler extension in Oracle SQL Developer; thus, we have to make sure that the data dictionary of our schema is imported into the data modeler in SQL Developer to be able to control how DDL scripts are generated?

Steps to Generate DDL Script for All Tables in SQL Developer

Open Export Data Modeler in DDL File
Select Database Version and Data Modeler
Select All Tables
Select the check box “Generate DDL in Separate Files” to save the tables script in separate physical files on our machine.
Select Primary Key and Unique constraints.
Select Indexes attached to each table
Select Foreign keys for each table
Select drop selection to include drop statements for each object like (Tables,Indexes, Foreign Keys, Materialize Views, Views, Sequences, Tablespaces)
Generate DDL Script

1. Open Export Data Modeler in DDL File

From SQL Developer go to File -> Data Modeler -> Export -> DDL File

How to Generate DDL Scripts for All Tables in SQL Developer Figure 1 — Figure 1

2. Select Database Version and Data Modeler

In the following window, we will choose the database version and specify the Data Modeler. The list of data modelers contains all data modelers that are imported from SQL Developer.

How to Generate DDL Scripts for All Tables in SQL Developer Figure 2 — Figure 2

3. Select All Tables

In this step, we will select all tables in our schema. and because we are working on HR schema we can see all tables for HR. in your case you will find all your tables under your schema.

How to Generate DDL Scripts for All Tables in SQL Developer Figure 3 — Figure 3

4. Select the check box “Generate DDL in Separate Files” to save the tables script in separate physical files on our machine.

There is an option to generate physical files on your machine that have the script for each table that you selected in the previous step.

5. Select Primary Key and Unique constraints.

Here we will have all primary keys and unique constraints that we want to export or include in our script.

6. Select Indexes attached to each table.

If we want to include the indexes on the tables we can select them from this tab. By default, all indexes will be selected because you have selected all tables.

7. Select Foreign keys for each table.

From here we can select which foreign keys we want to export or include in the script.

8. Select drop selection to include drop statements for each object like (Tables,Indexes, Foreign Keys, Materialize Views, Views, Sequences, Tablespaces)

Before each creates script of each table, index, foreign key, materialized view, view, sequence, or tablespace we may want to include a drop statement. in case we want to drop the object if it exists at the time of executing this script.

9. Generate DDL Script.

The final step is to generate the DDL script for all tables. Just save it somewhere for later uses.

How to Generate DDL Scripts for All Tables in SQL Developer Figure 4 — Figure 4

Conclusion

One of the common scenarios is to take the tables script, and in this post, we have covered in four simple steps how to generate DDL scripts for all tables in SQL Developer.

If you have any questions, please don’t hesitate to ask them in the comments section below. or ask your question from up right corner and an expert will reply your inquery.
If you have an addition to this guide to add extra value for the readers, kindly let me know.
Hopefully, it was clear and concise.