Finding the shortest route from diagram to tangible product can be tricky depending on the tool you use to create your Entity Relationship (database) diagrams. I'm using Visio 2003 Professional for mine, and so I went on the hunt for a way to transform diagrams into SQL 2005 Scripts (Visio Professional doesn't include any export features for ER diagrams). The final solution isn't the absolutely most elegant, but I'd call it live-able at least. The basic steps are:
1. Export your diagram to XML;
2. Apply an XSL stylesheet to the XML that generates SQL 2005-compatible scripts.
Exporting XML from VISIO 2003 Pro
Visio Professional doesn't include the luxury of exporting to XML, so I discovered a very sweet third party tool aptly called "Toolbox" made by a generous company named Orthogonal (the tool is free!). Installing Toolbox creates a floating toolbar in Visio that allows you to export the currently open ER diagram. To install simply download the Orthogonal "Toolbox" product, run the setup, restart Visio (if it was open during install), and open an ER diagram. The toolbox should be floating there in your window. If you don't see it, you can go to View,Toolbars and make sure it is checked as being visible.
Creating the SQL Scripts
In my scenario I opted to just go the route of using Internet Explorer to view the finished XML code since it will automatically apply any referenced stylesheets. Because Toolbox let us choose a stylesheet to apply at the time of export, a reference was added to the XML file so when we view it in IE we see a nicely formed SQL 2005 script to create our tables and their relationships. Here's a snapshot of the Toolbox dialog box:
The finished product:
On the subject of the XSLT...Orthagonal provides an XSLT file that creats the script, but without any linebreaks or anything to make it readable. I found a modification to this XSLT on this guy's site , and I modified it a little further in order to account for autoincrementing identity fields, table names that might also be reserved words (who ever does that?), and default values for bit type fields. My version is at the end of this post.
Final Notes
The XML exported by Toolbox doesn't seem to capture anything you put into the "default value" for entity columns. Because of this, I hard-code a rule into my XSLT that says if the field type is bit and NULL is NOT allowed, make the default value true. Also, although Visio does allow you to say that an column is an identity column, it does NOT give you a way to specify that it should autoincrement. I added one more rule to my XSLT that says if the column is INT and is IDENTITY, script out the autoincrement functionality as well.
The XSLT file has some HTML embedded in it in order to allow for an eye appealing output when viewed in IE. If you are going to be applying the stylesheet via another mechanism, you'll probably want to go through the XSLT and strip out the generated HTML first.
TIP: If you weren't aware of it, you can have multiple pages within one Visio ERD document (as in the screenshot at the beginning of this post). The Toolbox export will export one xml file for every diagram on every tab, so if you want to get things done in one shot just consolidate your Visio documents into one!
That should be it. Here are the relevant links for you to get started creating SQL 2005 scripts from your Visio 2003 ER diagrams:
my version of the XSLT file
link to get Toolbox
sample of the XML produced by Toolbox (without xslt applied)
sample of the XML produced by Toolbox (WITH xslt applied!)
You are not logged in, so your subscription status for this entry is unknown. You can login or register here.
This is great. Do you know if this will work with the Office 2007 Version?
One change to the XSLT though. The table names are escaped in table creation but not in the ALTER statements. Yes, I have a table named user, which is a reserved word.
So I had to add brackets around the xslt selects for the ALTER TABLE commands and the REFERENCES command.
Cheers.
By the way it seams to install and run fine in Visio 2007.
Anyone know how to get .Net 1.1 to allow access to the registry under Vista? This tool works fine on my developers XP machine but seams to hate my Vista box.