«
»

Reverse Engineer a DB [5] – ERwin

ERwin Data Modeler is a full-blown data modeling tool, allows for modeling on logical and physical levels, forward and reverse engineering of databases and data warehouses.

Step 1

Start ERwin and click on Tools –> Reverse Engineer.


 

Step 2

Select Logical/Physical for the New Model Type, and SQL Server for the Target Database.


 

Step 3

Under the Owners option, type in dbo; leave all other options as per default. Find some time to return to this dialog and explore all the options for reverse-engineering.


 

Step 4

The SQL Server Connection dialog opens, type in (or select) all the connection parameters and click Connect. The process starts as soon as the connection is established, you will see a progress bar and staus display during the reverse engineering process.


 

Step 5

Here is the first diagram, pretty good. Time to rearrange shapes and change few display options.


 

Step 6

The diagram on the left is on the Physical level and the one on the right is on the Logical level. You may have noticed that ERwin did not offer a list of tables, but has included all the tables owned by dbo, including the sysdiagrams table — in previous examples I have excluded the table. This is actually a correct behaviour, because the sysdiagrams is a part of the database and ERwin is a bi-directional tool, so it can be also used for database maintenance and refactoring.



Step 7

Expand Stored Procedures branch on the Model Explorer; make sure you are on the Physical level. Right-click on a procedure as select Properties.


 

Step 8

The SQL Stored Procedure Editor opens. All procedures staring with "p_" are mine, while those starting with "sp_" are part of the SQL Server diagram package, together with the sysdiagrams table. It is interesting to compare the Code and the Expanded tabs; with reverse-engineering, stored procedures are moved to model level — hence all the placeholders on the Code tab.


By poking around the Model Explorer, we can see all the reverse-engineered objects, including:

  • Tables
  • Relationships (foreign & primary keys)
  • Triggers
  • Stored procedures
  • Functions
  • Views
  • Logins
  • User IDs.

 

Category: Database