With Visio 2010, I can reverse engineer a database and get database schema. After I modify the database design, how can I export the database design data to a Excel file. Such as table column name, data type, etc.
Thanks,
You may try a report.
I try it. However it looks like the shape is locked. When I create new report, it doesn't allow me to select the table column or even table name.
I tried unprotect everything from "developer" manual. However the shape is still locked somehow. I can't pull anything from it.
Otherwise, it looks like the report is the solution.
Can you help me more.
Thanks!
Can you upload a file?
I attached two document:
1. order.vsd ----- It is just a example database modeling file which illustrate the database design in Visio
2. database.png ----- a screenshot which show the table columns and properties which I'd like to report.
Thanks a lot!
So I had some time to look for a solution to your problem.
Alas the results are very poor:
- I tried looking in the shapes both in visio itself and in an exported vdx with XML Notepad. MS did a good job in hiding the data. I gave up.
- Google does find a tool when you search for visio database forward engineering
http://forwardengineer.codeplex.com/
http://www.isummation.com/blog/forward-engineering-visio-2007-database-model-to-sql-server/
I didn't get it myself to work, but you may be more patient than me.
Sorry.
The database data (and UML data) is stored in a BLOB in binary form (in a hidden master named "Database model").
It's next to impossible to unpack this blob directly. But, there is a programmatic API (undocumented unfortunately, afaik) which allows you to access that data.
The API resides in the MODELENG.dll (it's in Visio folder) and provides COM API to read the data out of the DB and UML diagrams (that's the one that was removed in Vsio 2013)
The forward-engineering addin uses this MODELENGLib to access the diagram data.
Anyways, here is some VBA code sample, which extracts the following info using that MODELENGLib: table, columns, PKs, nullable, physical names..
If you want to extract full model information, including relationships and so on, you better consult the source code of the forwardengineer addin (ModelTransform.cs)
The code prints to the output window something like:
Table [customer]
Column [customerID], Type : "INTEGER", Nullable : False, PK : True
Column [firstName], Type : "VARCHAR(15)", Nullable : True, PK : False
Column [LastName], Type : "VARCHAR(30)", Nullable : True, PK : False
Table [product]
Column [productID], Type : "INTEGER", Nullable : False, PK : True
Column [productName], Type : "VARCHAR(30)", Nullable : True, PK : False
Column [price], Type : "DECIMAL(10,2)", Nullable : True, PK : False
Table [order]
Column [orderID], Type : "INTEGER", Nullable : False, PK : True
Column [customerID], Type : "INTEGER", Nullable : False, PK : True
Column [ProductID], Type : "INTEGER", Nullable : False, PK : True
Excel stuff is up to you I suppose... mind the reference to the MODELENG.dll !
Sub ExtractInfo()
Dim engine As New MODELENGLib.VisioModelingEngine
Dim models As MODELENGLib.IEnumIVMEModels
Dim modelEnum As MODELENGLib.IEnumIVMEModels
Set modelEnum = engine.models
Do
Dim model As MODELENGLib.IVMEERModel
Set model = modelEnum.Next
If model Is Nothing Then Exit Do
Call ExtractTables(model)
Loop
End Sub
Sub ExtractTables(model As MODELENGLib.IVMEERModel)
Dim tableEnum As MODELENGLib.IEnumIVMEEntities
Set tableEnum = model.Entities
Do
Dim table As MODELENGLib.IVMEEntity
Set table = tableEnum.Next
If table Is Nothing Then Exit Do
Debug.Print "Table [" & table.PhysicalName & "]"
Set pkColumns = CreateObject("Scripting.Dictionary")
Call ExtractTableKeys(table, pkColumns)
Call ExtractTableColumns(table, pkColumns)
Loop
End Sub
Sub ExtractTableColumns(table As MODELENGLib.IVMEEntity, pkColumns)
Dim columnEnum As MODELENGLib.IEnumIVMEAttributes
Set columnEnum = table.Attributes
Do
Dim column As MODELENGLib.IVMEAttribute
Set column = columnEnum.Next
If column Is Nothing Then Exit Do
Dim columnDataType As MODELENGLib.IVMEDataType
Set columnDataType = column.DataType
isPrimaryKey = pkColumns.Exists(column.PhysicalName)
Debug.Print " Column [" & column.PhysicalName & "], Type : """ & columnDataType.PhysicalName & """, Nullable : " & column.AllowNulls & ", PK : " & isPrimaryKey
Loop
End Sub
Sub ExtractTableKeys(table As MODELENGLib.IVMEEntity, pkColumns)
Dim annotationEnum As MODELENGLib.IEnumIVMEEntityAnnotations
Set annotationEnum = table.EntityAnnotations
Do
Dim annotation As MODELENGLib.IVMEEntityAnnotation
Set annotation = annotationEnum.Next
If annotation Is Nothing Then Exit Do
Select Case annotation.kind
Case eVMEEREntityAnnotationPrimary
Dim pkColumnEnum As MODELENGLib.IEnumIVMEAttributes
Set pkColumnEnum = annotation.Attributes
Do
Dim pkColumn As MODELENGLib.IVMEAttribute
Set pkColumn = pkColumnEnum.Next
If pkColumn Is Nothing Then Exit Do
pkColumns.Add pkColumn.PhysicalName, pkColumn
Loop
End Select
Loop
End Sub
I've also attached the diagram file you posted (take a look at the VBA code inside)
Very cool! Thanks for sharing.
very nice work,
al Edlund
Thanks a lot of the inputs!!! It really help.