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.
Start ERwin and click on Tools –> Reverse Engineer.
Select Logical/Physical for the New Model Type, and SQL Server for the Target Database.
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.
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.
Here is the first diagram, pretty good. Time to rearrange shapes and change few display options.
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.
Expand Stored Procedures branch on the Model Explorer; make sure you are on the Physical level. Right-click on a procedure as select Properties.
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:
- Relationships (foreign & primary keys)
- Stored procedures
- User IDs.