ThatJeffSmith

Reverse Engineer Views into an ERD

ERD – Fancy way of saying Entity-Relationship Diagram

Since version 10.0 of Toad for Oracle, users have been able to select a table and have an ERD generated that automatically includes any related tables via Referential Integrity.

However, what if you are trying to document a view which may bring in one or more other views and any number of tables? Fortunately Toad has a diagramming feature that is not limited to referential integrity, e.g. Foreign Key constraints.

This feature is known as the Code Road Map. Primarily used for identifying all objects used by a PL/SQL object (function, procedure, type, trigger, package body), the Code Road Map parses the base object looking for references to other database objects. Now, here is where it gets interesting. For each identifying object, it also parses its source and keeps going until it resolves everything required to be able to compile the original object without error.

For version 10.5 of Toad, you can now use the Code Road Map against a view and also build a ERD. It will pull in everything you need, and no need for foreign keys or references in the data dictionary ‘dependency’ views. Let’s look at an example.

Getting Started

  • Open the Schema Browser
  • Find your view
  • Mouse-right-click
  • Select ‘Code Road Map’
  • Right-clicking is Your Friend!

    The Model
    Once the Diagram has been built, you can zoom in or out until you get the ideal viewing experience. There is a really neat Loupe tool for zooming into just a piece of the model. You can also create sub-models to split out subsets of objects in case you have too much going on in the main diagram.

    Our View and 2 Tables

    Build a WYSIWYG Report
    Awww, my favorite acronym. Look it up. Use the ‘Report’ button to generate an HTML version of the diagram that is rendered exactly as it appears in Toad. This way you can share your models with anyone in your organization that has access to your Intranet, but not to Toad.

    Publish to your Sharepoint or Intranet for non-Toad Users