Visual Paradigm logo
Jump to Menu

How to Form ERD from DDL and Database Systems

Visual Paradigm enables you to create a database model from an existing database. Entity Relationship Diagram (ERD) graphically shows the structure of a database so you can see how database elements, such as tables and views, relate to each other without viewing the actual data. This can streamlines creating a new database or understanding the structure of an existing database.

Compatible edition(s): Enterprise, Professional, Standard

  • September 10, 2014
  • Views: 123,509
  • PDF

For existing database, ERD can be generated from database schema through reverse engineering. However, what if your database is not accessible to perform the reverse engineering? (i.e. the production database is not accessible for such purpose). So what is the alternative to achieve this, when we encounter this kind of situation?

Generating DDL file from Database

Actually, most of the Database Management System (DBMS) such as Oracle, Microsoft SQL Server, MySQL, etc... support to export the schema of database into a Data Definition Language (DDL) file. In this tutorial you will first learn how to reverse the DDL file, and then learn how to visualize your database in ERD by reversing engineering the DDL file. A video demo is also provided at the end of this tutorial.

Microsoft SQL Server will be used as an example throughout the tutorial. To generate DDL file for your SQL Server database:

  1. Open the SQL Server Management Studio and connect to your database.
    01 connect to sql server
  2. Right click on the database which you want to be reversed to and select Tasks > Generate Scripts...
    02 generate script
  3. Click Next in Generate and Publish Scripts dialog.
    03 generate script dialog
  4. Choose Select specific database objects, then choose all Tables in the list and press Next to proceed.
    04 select tables
  5. Specify the path for outputting the script file in the File name field.
    05 specify output path
  6. Select ANSI text in the Save as field, then press Next to proceed.
    06 select ansi text
  7. Review the selection and press Next to start generating DDL script.
    07 review
  8. Press Finish when the process is done.
    08 generate done

For other popular databases

MySQL

For MySQL, the database schema can be exported by using the mysqldump command with the following arguments.

Mysqldump -u %user% -p -no-data %db_name% > %path_to_script_file%

This will extract the schema of your specified database into a script file in the path you specified.

Oracle

For Oracle, the database schema can be exported by using the expdb command with the following arguments.

expdb %user_name%/%password%@%host% schema=%schema% dumpfile=%path_to_script_file% content=metadata_only

This will extract the schema of your specified database into the dump file path specified under the dumpfile argument.

PostgreSQL

For PostgreSQL, the database schema can be exported by using the pg_dump command with the following arguments.

pg_dump -h %hostname% -u %user_name% -port %port% --schema-only %database_name% > %path_to_script_file%

This will extract the schema of your specified database into a script file in the path you specified.

Reverse DDL file into ERD

Once we got the DDL file ready, we can then reverse it into ERD. To reverse your DDL file:

  1. Go to Tools > Database > Reverse DDL...
    09 reverse ddl
  2. Specify the path of the DDL file we exported in previous step in the File path field.
    10 specify ddl path
  3. Specify the database of the DDL file which was created from the Database combo box.
    11 select db
  4. Select Generate ERD check box to allow Visual Paradigm to generate the ERD for you automatically.
    12 generate erd
  5. Press OK to proceed.
    13 erd

After that, the ERD of your database will be generated and you can analyze it or simply work on it.

Watch this Tutorial on YouTube



Turn every software project into a successful one.