Visio > Software and database model diagrams > Database model diagrams
Reverse engineer an
existing database into a database model
With
the Reverse Engineer Wizard in Microsoft Office Visio Professional, you can
create a database model from an existing database or a Microsoft Office Excel
workbook. Database models graphically show the structure of a database so you
can see how database elements, such as tables and views, relate to each other
without showing the actual data. This can streamline creating a new database or
understanding the structure of an existing one.
What do you want to do?
Review what the Reverse Engineer Wizard extracts
Prepare to start the Reverse Engineer Wizard
Reverse engineer an existing database
Can't find the database modeling features?
Microsoft Office Visio Standard does not
include the Database Model Diagram template.
Microsoft Office Visio Professional supports
the reverse engineering features for the Database Model Diagram template (that
is, using an existing database to create a model in Visio) but it does not
support forward engineering (that is, using a Visio database model to generate
SQL code).
You can find the full suite of database
modeling features, including both reverse engineering and forward engineering,
in Visio for Enterprise Architects. Visio for Enterprise Architects is included
in MSDN Premium Subscription, which is available with Visual Studio
Professional and Visual Studio Team System role-based editions.
Review what the Reverse Engineer Wizard
extracts
The
schema definition information that the wizard can extract depends on a
combination of things, such as the capabilities of the database management
system (DBMS) and ODBC driver. The wizard shows all the elements it can extract
and let you choose which ones you want. For example you may only be interested
in 5 out of 10 tables, and 2 out of 4 views.
While
you are running the wizard you can choose to have it automatically create the
drawing in addition to listing the reverse engineered items in the Tables and
Views window. If you decide not to have the drawing created automatically, you
can drag the items from the Tables and Views window onto your drawing page to
manually assemble the database model.
To
the extent that they are available from the target DBMS, you can extract the
following:
Tables
Views
Primary keys
Foreign keys
Indexes
Triggers (including code)
Check clauses (including code)
Stored procedures (including code)
If
you are reverse engineering an Excel workbook, before you start the wizard you
need to open the workbook and name the group (or range) of cells that contain
the column headings. If you want to use more than one worksheet, just name the
group of column cells in each worksheet. These ranges are treated like tables
in the wizard. For more information on how to name a range of cells see the
topic Define named cell references or ranges in your Microsoft Office Excel
help.
For
best results, set your default driver to the target database that you want to
reverse engineer before you run the Reverse Engineer Wizard. This ensures that
the wizard maps the native data types correctly and that all the code extracted
by the wizard displays correctly in the Code window.
1.
On the Database
menu, point to Options and then click Drivers.
2.
On the Drivers
tab select the Visio-supplied driver for your DBMS. For example if you are
designing an Access database you would choose Microsoft Access.
Note If you are reverse engineering an
Excel worksheet, choose the ODBC Generic Driver.
3.
Click Setup.
4.
On the ODBC drivers
tab, select the check box for the vender supplied driver for your DBMA. For
example if you are designing an Access database you would select the Microsoft
Access Driver (*.mdb) check box.
5.
Click OK in
each dialog box.
Note A vendor-supplied 32-bit ODBC driver must be from an
ODBC Data Access Pack greater than version 2.0 and ODBC Level 1-compliant or
greater. A vendor-supplied OLE DB provider must be compliant with the OLE DB
version 1.0 or later specification.
Reverse engineer an existing database
1.
On the File
menu, point to New, point to Software and Database, and then
click Database Model Diagram.
2.
On the Database
menu, click Reverse Engineer.
3.
On the first screen of
the Reverse Engineer Wizard, do the following:
§ Select the Microsoft Office Visio database
driver for your database management system (DBMS). If you have not already
associated the Visio database driver with a particular ODBC data source, click Setup
to do so now.
Note If you are reverse engineering an
Excel worksheet, choose the ODBC Generic Driver.
§ Select the data source of the database you are
updating. If you have not already created a data source for the existing
database, click New to do so now.
When you create a new source, its name is added to the Data
Sources list.
§ When you are satisfied with your settings,
click Next.
§ Follow the instructions in any driver-specific
dialog boxes. For example, in the Connect Data Source dialog box, type a
user name and password, and then click OK. If your data source isn't
password protected, click OK.
4.
Select the check boxes
for the type of information that you want to extract, and then click Next.
Note Some items may be grayed out
because not all DBMS's support all the kinds of elements the wizard can
extract.
5.
Select the check boxes
for the tables (and views, if any) that you want to extract, or click Select
All to extract them all, and then click Next.
Note If you are reverse engineering an
Excel worksheet and don't see anything in this list, then it is likely that you
need to name the range of cells that contain the column headings in your
spreadsheet.
6.
If you selected the Stored
Procedures check box in step 5, select the procedures that you want to
extract, or click Select All to extract them all, and then click Next.
7.
Select whether you
want the reverse engineered items added automatically to the current page.
Note You can choose to have the wizard
automatically create the drawing in addition to listing the reverse engineered
items in the Tables and Views window. If you decide not to have the drawing
created automatically, you can drag the items from the Tables and Views window
onto your drawing page to manually assemble the database model.
8.
Review your selections
to verify that you are extracting the information you want, and then click Finish.
Note If you use the ODBC Generic Driver, you may receive
an error that indicates that the reverse engineered information may be
incomplete. In most cases this isn't a problem — just click OK and
continue with the wizard.
The
wizard extracts the selected information and displays notes about the
extraction process in the Output window.
See Also
Create a Database Model (also known
as Entity Relationship diagram)