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

You can see a short end-to-end demonstration of reverse engineering using Direct Connect here, or you can 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.

Reverse engineering can be performed using:

Direct Connect (available only for supported Cloud Platforms)
DDL upload from your database
Excel file upload

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

Navigation:

Back
Upload SQL script (from file or text box)
Direct DB Connection
Clear
Import

Methods:

(Direct) Connect to DW
Upload from a file (or paste into the text area)
Upload from Excel

Reverse Engineering methods

Reverse engineering can be performed through any of the following methods.

Direct Connect

If using a direct connection, see the related article ("Direct DW Connection") at the bottom of this post, then follow the rest of the steps described below.

File or script upload

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

Excel upload

Project objects can be bulk edited and uploaded through Excel using the SqlDBM template format.

SqlDBM provides two ways to upload information from Excel:

From the Reverse Engineering page
From the Database Documentation page

Although the Excel templates are the same, the two methods differ in the level of changes they permit. Database Documentation upload is meant for metadata changes and end-user project enrichment and, therefore, does not allow structural/physical changes (beyond adding comments/descriptions).

In both formats, editable fields are highlighted in yellow.

For more info on Database Documentation Excel upload, see the related article at the bottom of this post.

To begin, download the SqlDBM Excel template containing all existing project objects.

You can download the template from the diagram menu:

Or from the Reverse Engineering page.

This will download all the project objects and properties, and the file can be used to make modifications.

After making changes, import the Excel file as described above.

Import changes

Once objects have been added using one of the methods described above, upload them to SqlDBM to review and finalize the changes.

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()

Google BigQuery

The instructions for reverse engineering BigQuery are too detailed to list here and are described in a separate article ("Google BigQuery: Direct Connect and Reverse Engineer") linked at the bottom of this post.

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 Data Options #1 tab and check “Only schemas.”

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