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).

This saves me from having to do all my diagramming in SQL Server/Visual Studio 2010 or another product.  I know there are other products out there that are much better for building database models, but to build a simple data model, Visio does the job and customers love its colors and the ability to open the model with Office when I need to discuss it with them.

Don’t forget there is a “Reverse Engineer” option in Visio Professional and Premium editions to extract the database schema from an existing SQL Server database.  Note that to use this you must first create an ODBC data source using the SQL Server Native Client driver and connect it to the database you wish to extract the schemas from.

More info:

Video How To Use Microsoft Visio 2010 for Database Design

Visio 2010 Forward Engineer Add-in

About James Serra

James currently works for Microsoft specializing in big data and data warehousing using the Analytics Platform System (APS), a Massively Parallel Processing (MPP) architecture. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence/MDM architect and developer, specializing in the Microsoft BI stack. He is a SQL Server MVP with over 25 years of IT experience.
This entry was posted in SQL Server, SQLServerPedia Syndication, Visio. Bookmark the permalink.

8 Responses to Create SQL Server scripts with Visio 2010

  1. yadira says:

    Unfortunately that addin doesn’t work in a 64bit operative system. :(

  2. Thanks James,
    a greate tool. It helps to save hours and hours rewriting the database structure.
    Stefan

  3. Devin knight says:

    I wish I would have been reading your blog a year ago! I just found his last week. I got the 64 bit issue working the person mentioned here. If they look at the discussion in the codeplex project they’ll find the workaround.

  4. I went and looked for the post Devin Knight referred to, and found it. It does, indeed, seem to work on Win 7 / 64. Here is a link: http://forwardengineer.codeplex.com/discussions/250430#post889029

    Here is the critical piece of info:

    Go to “C:\Program Files (x86)\Visio Forward Engineer ” where its installed and LAUNCH “Visio.ForwardEngineer.vsto”.

    Once you do that, you will get a “success” dialog and when you fire up your database diagram in Visio 2010, you’ll get the “Forward Engineer” menu item as promised. Thanks Devin.

  5. Pingback: Entity Data Modeling with Visual Studio | James Serra's Blog

  6. Pingback: Entity Data Modeling with Visual Studio - SQL Server - SQL Server - Toad World

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>