The Reverse Engineering feature allows SqlDBM to automatically identify, relate, and diagram your entire database using its DDL script. This process can be used to start projects as well as synchronize the latest changes from the database for an existing project.

See a short end-to-end demonstration of reverse engineering here or proceed to the rest of this article for step-by-step instructions and notes for specific databases.

Reverse engineer

To begin the process, select the "Reverse Engineering" option on the left pane.

You will need to export the DDL from your database (see instructions for specific databases below). For Snowflake and Azure Synapse, a direct connection is available.

If using a direct connection, see the tutorial here, then follow the rest of the steps described below.

For reference, the menu items for this feature are as follows:

º Back

º Upload SQL script (from file or text box)

º Direct DB Connection

º Clear

º Import

Reverse Engineer Diagrams

Upload your DDL script in a file ("Drop your file" button) or paste it into the text editor.

Click the upload (up arrow) or the "Upload It" button to allow SqlDBM to parse the script.

Review the metadata summary of the objects to be imported on the top of the screen. You can see a breakdown of the tables and optionally select or unselect specific elements on the left.

Review the "Add Tables" options on the left to select which diagrams will be generated.

"Reverse Engineer Diagram" - generates an ER diagram with all the objects in the script and their relationships.
"RE: Schema Diagrams" - generates an ER Diagram per schema object in the DDL
"Diagram Name" diagram - adds the objects to the currently selected diagram in case of an existing project.

When you're ready, press the "Import" button or triangle icon on the top menu bar to finish Reverse Engineering your database.

How to generate DDL for specific databases

SqlDBM will display a database-specific step-by-step summary of how to generate the required DDL at the top of the Reverse Engineering screen, depending on project type. You can find detailed information for some databases below.

MySQL Workbench

To generate 'MySQL' DDL script:

1. Open MySQL Workbench

2. In Schemas Navigator, expand your schema and Tables under it, then select tables you want to import

3. Right click > "Send to SQL Editor" > "Create Statement"

Snowflake

At "Snowflake UI" type the following SQL in the query window

sample: SELECT GET_DDL('schema','"DATABASE_NAME"."SCHEMA_NAME"', true);

From Snowflake UI, copy results of your SQL

Databricks

Databricks supports using external metastores instead of the default Hive metastore.

You can export all table metadata from Hive to the external metastore.

Use the Apache Spark Catalog API to list the tables in the databases contained in the metastore.
Use the SHOW CREATE TABLE statement to generate the DDLs and store them in a file.
Use the file to import the table DDLs into the external metastore.

The following code accomplishes the first two steps.

%python

dbs = spark.catalog.listDatabases()
for db in dbs:
  f = open("your_file_name_{}.ddl".format(db.name), "w")
  tables = spark.catalog.listTables(db.name)
  for t in tables:
    DDL = spark.sql("SHOW CREATE TABLE {}.{}".format(db.name, t.name))
    f.write(DDL.first()[0])
    f.write("\n")
f.close()

Postgres

1. In pgAdmin, right-click on the database and click Backup.

2. Enter an appropriate path and filename (i.e.,/some/path/my_script.sql).

3. Select Plain as the format in the Format dropdown.

4. Go to the Dump Options #1 tab and check “Only schema.”

5. Then click Backup. Then click Done.

Sql Server and Azure Synapse

To generate 'Microsoft SQL Server' DDL script:

1. Open SqlServer Management Studio
2. In Server Explorer, right-click on your database and choose Tasks > "Generate Scripts…"

3. Make sure to enable the "Script Indexes" option by clicking "Advanced" in "Set Scripting Option."

For the complete instructions for each, please refer to the Microsoft documentation page for each:

MySQL

1. Open MySQL Workbench
2. In Schemas Navigator, expand your schema and Tables under it, then select tables you want to import
3. Right-click > "Send to SQL Editor" > "Create Statement"

Redshift

To CREATE TABLE/SCHEMA DDL for an already existing table or schema in Redshift, you need to follow the instructions provided by AWS.

STEP 1: Create view "v_generate_schema_ddl"

You can recreate the table/schema DDL by running scripts called v_generate_tbl_ddl.sql and v_generate_schema_ddl.sql.

You can download the scripts from amazon-redshift-utils, which is part of the Amazon Web Services - Labs git hub repository. Please use these scripts' GitHub links mentioned in the reference section below.

Reference:
1) https://github.com/awslabs/amazon-redshift-utils/blob/master/src/AdminViews/v_generate_schema_ddl.sql
2) https://github.com/awslabs/amazon-redshift-utils/blob/master/src/AdminViews/v_generate_tbl_ddl.sql

STEP 2: Run Query and download results
Example

select * from admin.v_generate_tbl_ddl where schemaname = 'SchemaName'

STEP 3: Copy the last column in a separate text file

STEP 4: Replace all double quotes