Wednesday, December 07, 2011

Community created Forward Engineer Database Diagrams Addin for Visio 2010 (Think "Diagram to T-SQL," like in past versions, but with source)

James Serra's Blog - Create SQL Server scripts with Visio 2010

"If you are using Visio 2010 and creating tables via the Database Model Diagram template, you may be surprised to learn there is not a way to build CREATE TABLE scripts from your resulting diagram. The last release that supported that was the Enterprise Architect version of Visio 2003, which required Visual Studio 2005 to be installed.

But there is help. There is an open source project from Alberto Ferrari that is an add-in for Visio 2010 that allows you to generate SQL Scripts from your database diagram. The resulting script has the create table statements, the alter table statements to add foreign key constraints and indexes, and even creates the code to drop objects if they already exist. Alberto’s blog post about it is here, and you can download the add-in at Visio Forward Engineer Addin (NOTE: I could not get it to install properly until I first installed Microsoft .NET Framework 4).

..."

Alberto Ferrari - Visio Forward Engineer Addin for Office 2010

"Most of my database model are written with Visio. I don’t want to start a digression whether Visio is good or not to build a simple data model: Visio is enough for my modeling needs and customers love its colours and the ability to open the model with Office when I need to discuss it with them. When I have finished modeling, I generate the database and everything works fine.

Nevertheless, Microsoft seems not to like the forward engineer capabilities of Visio. The last release that supports forward engineering is the Enterprise Architect version of Visio 2003, which requires Visual Studio 2005 to be installed on the box. Since I am really tired to install old releases of Visio just to perform forward engineer (moreover, the 2003 release does not support the new data types) I decided it was time to follow the standard approach to Microsoft products: “if Microsoft does not help, do not ask, do it by yourself”.

Thus, I wrote an Office add-in that performs forward engineer of a Visio database model to SQL Server. It does not support any other database driver and has some big limitations, since the library that should let programmers have access to the underlying Visio data model is non documented and full of uninplemented interfaces. Thus, I needed to collect information over the web, searching for people who tried the same before me. Nevertheless, for a standard data model it works fine and saves me to the need to install old software on new computers. :)

It has two basic functions, available through a new ribbon:

..."

CodePlex - Visio Forward Engineer Addin

"Project Description
Somehow Microsoft decided not to include this feature in 2010 version of Visio.

Visio Forward Engineer Addin for Visio 2010, adds the ability to generate the database scripts directly from the database model defined in Visio 2010. It is developed in C#.

Idea

The original idea of this project was developed by Alberto Ferrari*. The original project can be found at http://www.sqlbi.com/Default.aspx?tabid=173.

This project has two main versions:

  • Version 1.0 that is Alberto's original version that was cleaned and created an automatized setup.
  • Version 2.x that is a completely rewritten project that offers many new features and improved code base.

Version 1.0 is available for download, while version 2.x is still under development.

..."

When creating database schemas I like boxes and lines.... I like diagramming my tables and views up front and visually seeing the relationships. I've found that doing that helps me better think through the model and iterate it until it's done "enough". At work I use ERWin, but for my home projects, I've not found a good package. I keep whining to Microsoft that Data Projects (i.e. Data Dude, etc) should have diagramming, but still no joy. I mean we can diagram code, why not databases? (I know, I know, it's not that easy, but still). So I used to use Visio, but after upgrading to 2010 that feature seems to have gone away. So being lazy I just did it the hard way, script, to diagram.

When I saw this, I knew I had to capture it for the next time I need to build a database.

The project looks stalled (no check-ins since March 2011) but since the source is available, if there're any issues, well, we can fix it ourselves! :)

No comments: