THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Alberto Ferrari

Visio and SQL Extended Properties: a tool to marry them

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:

 Tp

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.

Published Wednesday, November 07, 2007 12:00 PM by AlbertoFerrari
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Mark said:

This tool sounds handy. Does your site (www.sqlbi.eu) still have it?

October 28, 2009 12:39 PM
 

AlbertoFerrari said:

Mark, www.sqlbi.eu is under maintenance in these days, if you drop me an e-mail alberto.ferrari at sqlbi.eu I'll send you the sources.

October 28, 2009 12:45 PM
 

Anibal Rodriguez said:

Alberto,

Could you please send me the sources please? my email is anibaldelucia@hotmail.com.

Thanks,

Anibal.

January 4, 2010 2:25 PM
 

Wendell Sá said:

Alberto,

Please, send me this tool.

delteixeira@hotmail.com

Thanks!

Wendell

March 19, 2013 2:15 PM

Leave a Comment

(required) 
(required) 
Submit

About AlbertoFerrari

Alberto Ferrari is a Business Intelligence consultant. He his interests lie in two main areas: BI development lifecycle methodologies and performance tuning of ETL and SQL code. His main activities are with SSIS and SSAS for the banking, manufacturing and statistical sectors. He is also a speaker in international conferences like European PASS Conference and PASS Summit.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement