Visio 2007 Database Model DDL Generation

Known Limitiations

The first known limitation (and probably my biggest concern) is that this script has only been tested with a limited sample of Visio Database Model Diagrams. Because of that, there may be unexpected data in your particular diagram. All I can say is, give it a try. It may still generate much of what you need and it will give me more samples to work from so that I can improve the script.

There is also a known limitation in the area of Foreign Keys. While I am able to correctly relate the Tables of a Foreign Key, I don’t have direct access to related Columns between the two tables. Consequently, I have had to guess at which columns match. Here’s how the matching currently works:

For each Foreign Key defined on the Child table

For each Child column in the Foreign Key

look for a column in the Parent table’s Primary Key that matches the end of the Child column name. E.g. Given a Foreign Key between related tables “Countries” (parent) and “People” (child) on Child Column “birth_country_id”, then “birth_country_id” in the “People” table will match to “country_id” in the “Countries” table.

If all of the Child columns match a column in the Parent table’s Primary Key and all of the Parent table’s Primary Key columns match a column in the Child table’s Foreign Key, then these are the Child table’s columns that are used for the Foreign Key.

I know it’s a bit hokie and it is also dependant on my data naming conventions, but it seems to work pretty well for me. I hope to expand this to use a better matching algorithm in the future.