Automatic import of table and column comments

[Dave Routledge]

Hi

I have reverse engineered an Informix database into VEA.

Informix does not support table and column comments in the data
dictionary. The standard practice is to hold them elsewhere, in this
case I have them in a few database tables.

I am now faced with the arduous task of manually copying all of these
comments into visio. There are thousands.

Does anyone have any bright ideas on how to automate this task?

Thanks

Dave

 

[Scot Becker]

Hi Dave,

Informix does not support table and column comments in the data
dictionary. The standard practice is to hold them elsewhere, in this
case I have them in a few database tables.

I am now faced with the arduous task of manually copying all of these
comments into visio. There are thousands.

Does anyone have any bright ideas on how to automate this task?

About the only option I can think of is to generate the appropriate report
(e.g. table report) with the notes/comments, save it as a file, and then
write some code to parse out the text and shove it into your tables.

HTH,
Scot.
................................................
Scot Becker

Orthogonal Software
www.orthogonalsoftware.com

ORM Blog: www.objectrolemodeling.com

To e-mail me, please use my first name at the domain listed above. All mail
sent to the reply-to address gets routed to a junk e-mail folder where it
will likely be deleted without being read.

 

[Dave Routledge]

Hi Scot

Thanks for your suggestion but my problem is the other way round. I am
reverse engineering - i.e. want to pull comments INTO into visio from
an external source.

As I understand it, your suggestion is a method of exporting comments
FROM visio into a database.

Cheers

Dave

 

[Matt M]

Hi Scot

Thanks for your suggestion but my problem is the other way round. I am
reverse engineering - i.e. want to pull comments INTO into visio from
an external source.

As I understand it, your suggestion is a method of exporting comments
FROM visio into a database.

Hi

Perhaps what Scot means is to write a VBA macro or similar for Visio that
parses the comments from a file (or database connection), writing them to a
custom property of the entity shape or to the Notes database property of the
relevant entity or column. The second option is by far the more difficult,
since it means using Visio's unsupported and largely undocumented modelling
API (there is some info at www.pdata.com/VME/VisioModelingEngine.htm).

Matt M.

 

[Scot Becker]

Hi Dave,

Thanks for your suggestion but my problem is the other way round. I am
reverse engineering - i.e. want to pull comments INTO into visio from
an external source.

As I understand it, your suggestion is a method of exporting comments
FROM visio into a database.

Sorry, I read your post wrong.

I don't think you have any other options.

Matt suggested writing some VBA to pull the comments into Visio... which
would work except (unfortunately) that part of the Visio meta-model is not
exposed by the modeling engine API.

Scot.
................................................
Scot Becker

Orthogonal Software
www.orthogonalsoftware.com

ORM Blog/Portal: www.objectrolemodeling.com

To e-mail me, please use my first name at the domain listed above. All mail
sent to the reply-to address gets routed to a junk e-mail folder where it
will likely be deleted without being read.

 

[Dave Routledge]

Hi

Thanks for all these suggestions.

Given that it's largely undocumented, I'm probably not up to tackling
the VBA / API method just yet but will try it if my current method
fails. It's a bit laborious but this is what I have come up with, and
it seems to work:

1. Reverse engineer Informix database into Visio.
2. Generate a SQL Server database schema from this model.
3. Use DTS to port the data in the "doc" tables containing the comments
over to the SQL Server database.
4. Write a T-SQL cursor loop on the "doc" tables to add table and
column comments to the SQL Server tables.
5. Reverse engineer the SQL Server model back into Visio, therefore
pulling these comments into the model.

Other than being time consuming, the only problem I have encountered
with this method so far is that Visio does not appear to generate any
existing table and column comments from the "Notes" in the model.
These, instead, are added as /*comments*/ in the DDL script. Are you
aware of an option in Visio that can be changed to generate these as
actual table and column comments?

Thanks again,

Dave

 

[Scot Becker]

Other than being time consuming, the only problem I have encountered

with this method so far is that Visio does not appear to generate any
existing table and column comments from the "Notes" in the model.
These, instead, are added as /*comments*/ in the DDL script. Are you
aware of an option in Visio that can be changed to generate these as
actual table and column comments?

This has been asked before in this newsgroup, but I don't recall the exact
answer... I seem to recall that Visio will not generate the column comments.
I'd do a search to confirm; I could be wrong on that.

Scot.
................................................
Scot Becker

Orthogonal Software
www.orthogonalsoftware.com

ORM Blog/Portal: www.objectrolemodeling.com

To e-mail me, please use my first name at the domain listed above. All mail
sent to the reply-to address gets routed to a junk e-mail folder where it
will likely be deleted without being read.

 

[Dave Routledge]

Will do. Thanks for your help.

Dave

 

[Bill MacLean]

RE: Comments

The Visio driver setup dialog box (Datbase|Options|Drivers|Driver Misc
(tab) ) has a radio button that allows to specify if Visio will create
actual comment DDL. It works for Oracle and DB2.

It doesn't work for SQL Server. I always assumed that SQL Server just
didn't support comments through DDL. Here is an Oracle example:

COMMENT ON COLUMN TABLE.COLUMN IS 'The comment goes here';

What does a SQL server comment definition statement look like?

Thanks,

Mac

 

[Dave Routledge]

Hi Bill

That's interesting about the Oracle and DB2 option.

Regarding your SQL Server question, the syntax looks a bit odd, as
below. You can apparently add a whole set of comments to an object by
using other comment names instead of N'MS_Description'.

-- add table comment
DECLARE @v sql_variant
SET @v = N'SCRIPT COMMENT PROG'
EXECUTE sp_updateextendedproperty N'MS_Description', @v, N'user',
N'dbo',N'table', N'programmes', NULL, NULL
GO


-- add column comment
DECLARE @v sql_variant
SET @v = N'SCRIPT COMMENT PROG COLUMN'
EXECUTE sp_updateextendedproperty N'MS_Description', @v, N'user',
N'dbo',N'table', N'programmes', N'column', N'prg_serial'
GO

Thanks

Dave

 

[Peter]

Wow, I have been looking for this for so long... thank you so much. Why
can't they put this in the documentation. This is one of the most basic
tasks, i.e. reverse-engineer, edit --including column notes-- and write it
back to the db. Microsoft that owns Visio and MS-SQL can't seem to get a
simple implementation for this feature, I'm just amazed. I will try what is
suggested in the next post, but it certainly doesn't look easy.

Thanks for sharing this little, but important detail as to how to update
column notes ! I hope somebody at Microsoft's Visio team reads this.