Using Microsoft Visio to Reverse Engineer a Database

4

This article steps you through using Microsoft Visio’s Reverse Engineer feature to reverse engineer relational databases like Citrix’s Resource Manager Summary Database.

There are times when you want to get a graphical representation of a relational database (say, for instance, the Citrix Resource Manager Summary Database). You could wade through a DBMS management console (such as the Microsoft’s Enterprise Manager for SQL) to get a list of all the tables and primary key/foreign key relationships, then draw out these tables in some kind of graphics software. Or, you could use Microsoft Visio to do all this work for you. All you need is an ODBC connection and Microsoft Visio Professional.

  • Open Microsoft Visio Professional and create a new Database Model Diagram.
  • Click on the Database menu and select Reverse Engineer to start the Reverse Engineer Wizard.
  • Select an existing data source (DSN) or create a new one.
  • Select the information you want to add to the drawing. (I chose just the tables, primary keys, and foreign keys in this example).
  • Select the checkboxes for the tables (and views, if any) that you want to graph.
  • Select whether you want Visio to add the shapes to the drawing. If you don’t let the wizard add the shapes to the page now, you will be able to drag and drop the shapes onto the drawing after the wizard finishes extracting the information.
  • Review the selections made and click Finish.

I used the steps in the example demonstrated above to extract the Citrix Resource Manager Summary Database schema. As you can see from the screen shot below, Visio did a great job of extracting the information, but you’ll have to do quite a bit of moving shapes around to get a better picture of the relationships. Fortunately, I’ve already done this for you, and you can download the drawing in the Downloads section here

Click for a larger version
(Click for a larger version) 

One last really cool thing. You can also use Visio’s database features to generate databases from a drawing as well as update databases from a drawing. I often reverse engineer a database, make changes to the drawing, and let Visio make the changes to the database for me. That way my documentation and database structure stay in synch.

4 thoughts on “Using Microsoft Visio to Reverse Engineer a Database

  1. Hi there,

    I am trying to generate a report from the database.

    I am using Visio 2010. I would like to include the
    table names, column names , column types and the comments that I
    entered in the NOTES field associated with the tables and columns.
    Basically, I would like to generate a data dictionary report.

    how can i do this in visio ?

  2. This article helped me convince management to let me develop ERDs of our database using Visio in a Citrix Workspace. It worked perfectly. However, I need to convert my database ERDs to basic Visio diagrams that can be edited and modified for presentation outside of the Citrix environment. I performed a Copy, then paste into a basic Visio drawing, but attempts to edit text returned a message that it requires a database connection.

Leave a Reply

Your email address will not be published. Required fields are marked *