Reverse Engineering a SQL Server Database using Visio
Introduction
A best practice for development and database teams is to create and maintain an updated data model of each database.
The MS Visio is a product that help this task. We will show in this article how to make the Reverse Engineering a SQL Server Data Model to speed up their documentation process. This demo will use the Visio 2010 version.
This feature enables you to keep a Data Model contents always updated and everyone manipulates one or more Database tables can always refer a very reliable information source for the solutions development.
This documentation also helps to define different actions for maintenance and software development. This makes the reverse engineering task even more necessary, particularly in large IT teams.
Creating a Visio new diagram
So we can perform a Reverse Engineering existing Database Tables using MS Visio, we first need to create a new Data Model Diagram.
We will demonstrate step by step how to create this diagram and how to use this important resource for managing a database.
To start, click the "File" menu and select "New" option, so Visio will provide a new Model Diagrams window (Image 1).
Image 1 - See this Visio menu in the image below
The Model Diagrams displayed on this screen are pre-installed with Visio. In this Reverse Engineering task, we need to select the "Software and Database" option (Image 2).
If this option is not available, simply use the option to "Add/Remove Programs" in "Control Panel" on Windows OS and reinstall this "Models" option.
Image 2 - See this Template window in the image below
Once you select the "Software and Database" model, will be displayed several diagrams to your choice.
In this sample, we use the "Database Model" diagram (Image 3), that will enable to recognize all objects related to SQL Server Database. Double-click on diagram and a new blank page will be available for create our tables.
Image 3 - See this output SQL script in the image below
We will not modify, any property on this blank page and not add other objects. This is because we can lose some of the changes and customizations.
If you want to modify and customize document and/or objects layout make this change after Reverse Engineering implementation.
Let's begin the Reverse Engineering of our database through Visio at new page Data Model diagram, click on the "Database" menu and select "Reverse Engineer" button (Image 4).
Image 4 - See this menu option in the image below
Using the Wizard
Soon as you click "Reverse Engineer" button initializes an Reverse Engineering wizard to get structure object selected for import into your new document.
The first configuration you may define to get your table structure will select "Microsoft SQL Server" driver and configure the "Data Source" to use(Image 5).
Image 5 - See this Drivers and Data Source window in the image below
The "Data Source" settings can be performed through a DSN file or "ODBC Data Sources" program. This task is common to Developers and Database Administrators, then I will not go into detail about it.
When you define your "Data Source", click on the "Next" button to display the "Types of objects" window that will be imported into Visio document (Image 6).
We will select the "Tables" option and "Indexes" sub option, and the Wizard to get Tables and their Indexes directly on Database. Do you can follow our Wizard settings, just click the "Next" button.
Image 6 - See this type object window in the image below
The Wizard will open a window with all tables on your database. Do you have option to select one or more tables for this Reverse Engineering task, to use all tables, click the "Select All"(Image 7).
This option is interesting because you can create several documents in Tables groups or other objects on Database. When finished Tables settings on "Data Model" diagram, then click "Next" button.
Image 7 - See this "tables and/or views" window in the image below
Finally, the Reverse Engineer Wizard displays all Tables that you selected for import into Visio (Image 8). This process will turn the Tables on Visio objects and their properties, including the Indexes definitions.
Image 8 - See this Finish wizard in the image below
After you run the Reverse Engineering, the Tables are created in your Visio document (Image 9) with their fields, including details as data type and others. All Indexes on your tables are highlighted in bold.
When the Tables have primary key, will also display bold font beside field name identification.
Image 9 - See this Visio document in the image below
After you run the Reverse Engineering is possible customize your Tables, including details and relationships about your Fields and Indexes.
Conclusion
Do you can choose more than one way to get information on Tables is very useful, especially if this alternative to get the data structure is simple and quick to implement.
The Database Reverse Engineering using MS Visio is a feature that enables maintain your documentation always updated, without need to collect this information by an SQL Server expert user.
This helps to save development time, in different task analysis between everyone involved in the use of the data and It's database structure.
References
- Library Office - Reverse engineer an existing database into a database model
- Library Office - Reverse engineering an existing database
See Also
- Using Visio 2010 BPMN to Design, Model and Document
- Visio Management Pack Designer Example
- Visio Portal
Other Languages
This article was awarded the gold medal in the TechNet Guru of July 2014