SQL Management Studio: How to Reverse Database Schema into Entity Relationship Diagram without connecting to Database

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 OracleMicrosoft SQL ServerMySQL, 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:
  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.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s