Visio database models have column descriptions, SQL Server has extended properties where to store them but, even if they share the same producer, it seems that Visio is unable to store column descriptions in extended properties so that other tools can gather the same information easily and produce a decent documentation of a database.
After some browsing on the web, I was unable to find a tool that let Visio save the table and column descriptions in SQL Server, so I decided it was time to code it by myself.
First you need to generate the DDL script of the database having DDL Script commens ON for all the columns (which can be configured in the driver options) as in the following picture:
In the DDL file, VISIO adds header of comments to each table like this:
/* Create new table "Sales.Dim_Customers". */ /* "Sales.Dim_Customers" : Contains all the customer with various attributes used */ /* to analyze them. Does not contain (bla bla bla) */ /* "ID_Customer" : Primary key of the table */ /* "CustomerCode" : Customer code as seen in the OLTP database */ /* "ID_Geography" : Key in Dim_Geography. It is not shown to the end user */ /* "MaritalStatus" : Description of the marital status (Single / Married) */ /* "Gender" : Description of the gender (Male, Female) */ ... ... ... create table "Sales.Dim_Customers" ( "ID_Customer" int identity not null, "CustomerCode" nvarchar(15) not null, "ID_Geography" int not null, "MaritalStatus" nvarchar(20) not null, "Gender" nvarchar(20) not null)
I have written a simple parser (GenerateExtendedAttrib) that looks for comments in this form in a DDL file (starting with “Create new table”, then checks for column names and so on) and will finally generate the sp_addextendedproperty calls to define all the column and table descriptions.
The code fragment above generates this:
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Contains all the customer with various attributes used to analyze...' , @level0type=N'SCHEMA', @level0name=N'Sales', @level1type=N'TABLE', @level1name=N'Dim_Customers'; EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Primary key of the table' , @level0type=N'SCHEMA', @level0name=N'Sales', @level1type=N'TABLE', @level1name=N'Dim_Customers', @level2type=N'COLUMN', @level2name=N'ID_Customer';
You can call the utility with “/Append” in order to have the property definitions appended to the original file (which, in turn, can be run to have the database created) or you can call it without parameters, in this case the code is written to the console and you can redirect it wherever you want.
The tool can be downloaded at www.sqlbi.eu and is provided with sources (it is indeed a very simple yet effective gadget) so you can adapt it to whatever your needs are.
Have fun and, if you make any change to the code that might be useful, send me a note so I can update the public release.