Reverse Engineer a DB [4] – Visio

The professional edition of MS Visio has a database reverse-engineering feature; see the edition comparison chart. If new to the series, you may want to scroll down to the first two posts of the series, where I describe the sample database and the ODBC connection setup. What follows is a step-by-step reverse engineering procedure.

Step 1

Start a new file by selecting File –> NEW –> Software and Database –> Database Model Diagram.


Step 2

Once a new file opens, give it a name and save the file. After that, select Database –> Reverse Engineer from the top menu bar.


Step 3

The Reverse Engineering Wizard opens. First we have to select a driver to connect to the database — select Microsoft SQL Server from the list.


Step 4

When the driver is selected, the Data Source list is populated with entries from the ODBC  list which use the driver. I have added the database to the ODBC list in a previous post — select CodeDB from the list.


Step 5

Enter the password and click OK to continue.


Step 6

Select which objects to reverse-engineer. Visio offers tables, views, indexes, constraints and code (stored procedures and functions). Select all and click Next.


Step 7

A list of tables to include; as in the previous example (post) I have skipped the sysdiagrams table.


Step 8

A list of stored procedures to include. There is a small trick here: procedures starting with ‘sp_’ are internal to SQL Server. It is a bad practice to name your procedures using the ‘sp_’ prefix, so mine all start with  ‘p_’.


Step 9

Select Yes to add object shapes to the current page.


Step 10

Review the summary and click Finish.


Step 11

The initially generated diagram (left) looks quite good. By rearranging shapes and tinkering with the display options, I have created the one on the right. When moving shapes around, make sure not to accidentally disconnect a connector line from a shape: the connector represents a relationship object between tables, and disconnecting a line may remove a foreign key from a table.


Step 12

To inspect table columns, keys, triggers etc., click on a table in the diagram or in the table list.


Step 13

To examine stored procedures, switch to the code tab and double-click on a procedure name; the code editor opens. Among other features, the editor allows for specification of a mirror file in which to store the code outside Visio.