Reverse Engineering PowerDesigner 16.5 - SQLServer 2012 tables Extended Properties

[Former Member]

Hello all,

Sorry in advance if this basic question has already been asked. I have had a look at the forums first, as well as the documentation, without finding the answer I am looking for.

I am new to PowerDesigner 16.5, currently testing the reverse engineering function with a SQL Server 2012 database

I am trying to find how to import the table extended properties that were applied to the sql server tables (sp_addextendedproperties)

If it has already been imported with the reverse engineering default options, I can't see it anywhere in the resulting diagram.

Thank you in advance for your help

[Former Member · Accepted solution]

Hi all,

I have finally been able to find where the problem was.

If you  perform a reverse engineering of the MSSQL  'AdventureWorks' demo database, which tables have plenty of extended properties, the extended property value is available in the diagram in : table properties->General->Comments Section (picture below)

My understanding is you need to name your SQL Server  table Extended Property : 'MS_Description',  and define only one entry. That way, the PowerDesigner Reverse enginering function will  interpret it correctly.

If your SQL Server table holds multiple extended properties, or if you have only one, named differently than the standard 'MS_Description', it won't be interpreted by PowerDesigner.

There may be a way to import multiple extended properties with any name you want, but I don't know how to configure PowerDesigner to do so.

Anyway, this is the answer I was looking for, and I will recommend my customer to strictly follow the standard rules Microsoft implemented in SQL Server when they document their tables using extended properties.

Thanks again for your answers and time 🙂

[GeorgeMcGeachie]

With that small clue, I was able to find the places to configure reverse-engineering.

In the database definition file for SQL Server 2014, look in the section Script - Objects - Table - SqlListQuery

It contains the following code, which you could probably configure:

{OWNER, TABLE, TABLE_TYPE, COMMENT}

select

   u.name,

   o.name,

   case (o.type) when 'S' then 'SYSTEM TABLE' else 'TABLE' end,

   convert(varchar(8000), p.value)

from

   [%CATALOG%.]sys.sysobjects o

   join [%CATALOG%.]sys.schemas  u on (u.schema_id = o.uid)

left outer join [%CATALOG%.]sys.extended_properties p on (p.major_id = o.id and p.minor_id = 0 and p.name = 'MS_Description')

where

   o.type in ('U', 'S')

[  and u.name = %.q:OWNER%]

order by 1, 2

There's also mention of it in TableComment and ColumnComment, and in the View section - you can use the 'Find in Items' tool on the toolbar to find references to MS_Description.

[Former Member]

Thank you George for your answer and your time.

I will have a look at it today, and will try to configure the reverse engineering the way you described.

I will send you an update with the results I have obtained, whatever they are.

Thanks again for your precious help. Have a good day!!!

Thierry

[former_member185199]

Hi Thierry

When you open a table in your reversed model, have you clicked the "more>>" button

on the down/left side of the tableproperties-window ?

You should see then see  a tab with the Extended properties

can make a prinscreen now because i have only PD Reader here, but i used These Features a year or so ago and it worked fine

HTH

DJ

[Former Member]

Hi Dirk,

thank you for your answer 🙂

Very interesting. Checking the table properties after reverse engineering is the first thing I have checked, but I have not seen any 'extended properties' tab, which may suggest that they were not imported during the process.

Another explanation could be that the version of PowerDesigner I'm using (PowerAMC, the PowerDesigner's french version) does not support that feature. To be honest, I would be more than surprised if it were the case.

For your information, you will find below a screen capture of what I'm seeing (in french). No trace of 'Propriétés étendues' (extended properties)

Thank you again for your answer 🙂

Thierry

[GeorgeMcGeachie]

I'm not familiar with SQL Server, so I'm not sure what you mean by extended properties, but here goes anyway .

Have you checked the PowerDesigner help topic?

     Data Modeling > DBMS Definition Reference > Microsoft SQL Server

For a SQL Server database, every Table and Column has an extra tab on the property sheet, called 'Microsoft', which is where you'll find properties that are specific to SQL Server. They will probably not appear on a diagram by default, though you could display them if you want to.

[Former Member]

Hello George,

Thank you for taking the time to answer 🙂

Sure, I have checked the Powerdesigner help topic you mention (and the vast majority of online ressources provided by SAP) without finding any clue of what's going wrong.

I have made sure to review the physical model after the reverse engineering is completed, and every tab in the table properties  (the Microsoft tab gives information about partitioned tables, filegroups, filestream and compression. No trace of table extended properties).

I have also reviewed the different database options and tabs available when you configure the reverse engineering process.

There's a chance I miss something, since I am an absolute beginner with PowerDesigner, but I can't say what.

For your information, you will find an example of SQL Server extended properties in the image below.

Thank you again for your answer, very appreciated 🙂

Thierry