I need support for SQL Server user-defined table type

[Former Member]

Database: Microsoft SQL Server 2012

PowerDesigner 16.5

This is SQL server code: CREATE TYPE dbo.ListOfInt AS TABLE(val int NULL);

This is PowerDesigner reverse ingeneering: create type dbo.ListOfInt  from char(10) not null

Do PowerDesigner support user-defined table type?

Thank you,

Dmitriy

TableTypeSql.txt.zip

[marc_ledier]

Hi,

This is a bug.

You can workaround it by editing the XDB (Database Definition File).

Here are the steps, having your SQL 2012 model open:

* Open menu "Database > Edit Current DBMS...."

* On left pane, navigate to Script\Objects\Abstract Data Type\Create

You can see the definition:

<<

create type [%QUALIFIER%]%ADT%[%TYPE%=CLR?

   external name [%Assembly.GeneratedName%[.%Class%]]:

   as table (

      %ADTDEF%

   )]

>>

You need to replace it with

<<

create type [%QUALIFIER%]%ADT%[%TYPE%=CLR?

   external name [%Assembly.GeneratedName%[.%Class%]]:[%TYPE%=Table?

   as table (

      %ADTDEF%

   )]]

>>

* Hit OK and try reverse again.

Notes:

* To make it persistent, you need to create your own xdb using the SQL 2012 as template and perform the change on your xdb. See documentation (DBMS Definition Files) for more details

* The change consist in adding the assignment of %TYPE% variable to Table when the syntax match the "as table..." syntax.

* This change will be available in official product starting with 16.5 SP04 PL02

[Former Member]

Doesn't work on my system.

I change Script\Objects\Abstract Data Type\Create to

create type [%QUALIFIER%]%ADT%[%TYPE%=CLR?

external name [%Assembly.GeneratedName%[.%Class%]]:[%TYPE%=Table?

as table (

%ADTDEF%

)]]

But reverse still generate

/*==============================================================*/

/* Domain: ListOfInt                                            */

/*==============================================================*/

create type dbo.ListOfInt

   from char(10) not null

[Former Member]

Related question

How can I create (not reverse) Abstract Data Type in PowerDesigner?

For example this one?

CREATE TYPE DDD

   AS TABLE (

      ATTRIBUTE_1 CHAR(10),

      ATTRIBUTE_2 CHAR(10)

   )

go

[marc_ledier]

Hi,

On the General page of the ADT, you need to select Type=Table. This adds a new tab "Attributes" on the ADT properties page. Fill it as needed and it should work.

By the way, the change of the "Create" item does work on my version (16.5 SP04).

You need to make the change before you reverse the SQL (this item is used in reverse and generation).

Marc