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:
- 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.
After that, the ERD of your database will be generated and you can analyze it or simply work on it.
