How to Reverse Database Schema into Entity Relationship Diagram without connecting to Database

Entity Relationship Diagram (ERD) is a well-known diagram used to present the structure of database in a visualized form which helps you to study the database and understand the structure of your data model. For existing database, ERD can be generated by 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). 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. With Visual Paradigm, you can then reverse the DDL file and generate ERD from it. In this tutorial, you will learn how to visualize your database in ERD by reversing the engineer in DDL file.

September 10, 2014
User Rating:/ 2
Views: 15,269
PDF Link	Add comments
Edition: Standard or above (Edition comparison)

Generating DDL file from Database

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

Open the SQL Server Management Studio and connect to your database.
Right click on the database which you want to be reversed to and select Tasks > Generate Scripts...
Click Next in Generate and Publish Scripts dialog.
Choose Select specific database objects, then choose all Tables in the list and press Next to proceed.
Specify the path for outputting the script file in the File name field.
Select ANSI text in the Save as field, then press Next to proceed.
Review the selection and press Next to start generating DDL script.
Press Finish when the process is 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:

Go to Tools > Database > Reverse DDL...
Specify the path of the DDL file we exported in previous step in the File path field.
Specify the database of the DDL file which was created from the Database combo box.
Select Generate ERD check box to allow Visual Paradigm to generate the ERD for you automatically.
Press OK to proceed.