9. PD reverse engineering-from database to model (ER diagram)

Steps:

1. In Control Panel--admin tool (if no administrative tool found, view to large icon)--Data source (ODBC)--User DSN--Add a data source under User data source (here, add data sources as appropriate)

The orcle in the figure is the added data source, because the data source where my database resides is orcle.

2. Open PowerDesigner New PDM model

3, select the database under the Connect,datasource select Odbcmachine data Source, the following select our first set up data sources sql2008,

Then enter the user name and password of the login database at login:

4. Modify the Model data import statement and select General->microsoft SQL Server 2005->script->objects->table->sqllistquery

In fact, edit the import data model and database to the table, the original is set cannot import table description, so made the following changes:

System Default Code:

{owner,table, table_type, COMMENT}

Select
U.name,
O.name,
Case (O.type) if ' S ' then ' SYSTEM table ' else ' table ' end,
(select CONVERT (varchar (8000), value) From::fn_listextendedproperty (null, ' user ', u.name, ' table ', o.name, NULL, NULL) WHERE name = ' ms_description ') as Coln
from
[%catalog%.] Sys.sysobjects o
join [%catalog%.] Sys.schemas u on (u.schema_id = o.uid)
where
O.type in (' U ', ' S ')
[and U.name =%.q:owner%]
ORDER by 1, 2

The Modified code:

{owner,table, table_type, comment,tname}

Select
U.name,
O.name,
Case (O.type) if ' S ' then ' SYSTEM table ' else ' table ' end,
(CONVERT (varchar (8000), IsNull (F.value,o.name)),
CONVERT (varchar (8000), IsNull (f.value,o.name))
from
[%catalog%.] Sys.sysobjects o
join [%catalog%.] Sys.schemas u on (u.schema_id = o.uid)
Left join [%catalog%.] SYS.EXTENDED_PROPERTIESF on
f.major_id=o.id and F.minor_id=0 Andf.class=1
where
O.type in (' U ', ' S ')
[and U.name =%.q:owner%]
ORDER by 1, 2

5, modify the Model field import statement, select General->oracle10g->script->objects->column->sqllistquery

In fact, edit the import data model and database field correspondence, the original is the setting cannot import the field description, so made the following changes:

System Default Code:

{owner,table, S, COLUMN, Dttpcode, LENGTH, SIZE, PREC, COMPUTE, Notnull, Identity,domain, DEFAULT, Extidentityseedinc, COM ment, Extcollation,extidtnotforreplication, Extdeftconstname, Extrowguidcol}

Select
U.name,
O.name,
c.column_id,
C.name,
Case is c.system_type_id in (165, 167,231) and c.max_length =-1 then t.name + ' (max) ' Else T.name end,
C.precision,
Case (c.max_length) when-1 then 0 elsecase if c.system_type_id in (the, 231, 239) then (C.MAX_LENGTH/2) Else (c.max_ Length) end end as Colna,
C.scale,
Case (c.is_computed) when 1 thenconvert (varchar (8000), (select Z.definition from[%catalog%.] Sys.computed_columns Z where z.object_id = c.object_id andz.column_id = c.column_id)) Else ' end as COLNB,
Case (c.is_nullable) If 1 Then ' NULL ' Else ' notnull ' End,
Case (c.is_identity) at 1 Then ' identity ' else ' end,
Case when (c.user_type_id <>c.system_type_id) then (select D.name from [%catalog%.] Sys.types d whered.user_type_id = c.user_type_id) Else ' end as COLNC,
CONVERT (varchar (8000), d.definition),
Case (c.is_identity) when 1 thenconvert (varchar, i.seed_value) + ', ' + convert (varchar, i.increment_value) Else ' en D as Colnd,
(select CONVERT (varchar (8000), value) From::fn_listextendedproperty (NULL, ' user ', u.name, ' table ', O.name, ' column ' ', c.name) where name = ' ms_description ') as Colne,
C.collation_name,
Case (i.is_not_for_replication) at 1 Then ' true ' else ' false ' end,
D.name,
Case (C.is_rowguidcol) while 1 Then ' true ' else ' false ' end
from
[%catalog%.] Sys.columns C
join [%catalog%.] Sys.objects o on (o.object_id = c.object_id)
join [%catalog%.] Sys.schemas u on (u.schema_id = o.schema_id)
join [%catalog%.] Sys.types T on (t.user_type_id = c.system_type_id)
Left outer join[%catalog%.] Sys.identity_columns i on (i.object_id = c.object_id andi.column_id = c.column_id)
Left outer join[%catalog%.] Sys.default_constraints D on (d.object_id = c.default_object_id)
where
O.type in (' U ', ' S ', ' V ')
[and U.name =%.q:owner%]
[and O.name=%.q:table%]
ORDER by 1, 2, 3

Modified code:

{owner,table, S, COLUMN, Dttpcode, LENGTH, SIZE, PREC, COMPUTE, Notnull, Identity,domain, DEFAULT, Extidentityseedinc, COM Ment,colnname, Extcollation,extidtnotforreplication, Extdeftconstname, Extrowguidcol}

Select
U.name,
O.name,
c.column_id,
C.name,
Case is c.system_type_id in (165, 167,231) and c.max_length =-1 then t.name + ' (max) ' Else T.name end,
C.precision,
Case (c.max_length) when-1 then 0 elsecase if c.system_type_id in (the, 231, 239) then (C.MAX_LENGTH/2) Else (c.max_ Length) end end as Colna,
C.scale,
Case (c.is_computed) when 1 thenconvert (varchar (8000), (select Z.definition from[%catalog%.] Sys.computed_columns Z where z.object_id = c.object_id andz.column_id = c.column_id)) Else ' end as COLNB,
Case (c.is_nullable) If 1 Then ' NULL ' Else ' notnull ' End,
Case (c.is_identity) at 1 Then ' identity ' else ' end,
Case when (c.user_type_id <>c.system_type_id) then (select D.name from [%catalog%.] Sys.types d whered.user_type_id = c.user_type_id) Else ' end as COLNC,
CONVERT (varchar (8000), d.definition),
Case (c.is_identity) when 1 thenconvert (varchar, i.seed_value) + ', ' + convert (varchar, i.increment_value) Else ' en D as Colnd,
(select CONVERT (varchar (8000), value) from[%catalog%.] Sys.extended_properties as ex where ex.major_id = c.object_id andex.minor_id = c.column_id),
(select CONVERT (varchar (8000), value) from[%catalog%.] Sys.extended_properties as ex where ex.major_id = c.object_id andex.minor_id = c.column_id),
C.collation_name,
Case (i.is_not_for_replication) at 1 Then ' true ' else ' false ' end,
D.name,
Case (C.is_rowguidcol) while 1 Then ' true ' else ' false ' end
from
[%catalog%.] Sys.columns C
join [%catalog%.] Sys.objects o on (o.object_id = c.object_id)
join [%catalog%.] Sys.schemas u on (u.schema_id = o.schema_id)
join [%catalog%.] Sys.types T on (t.user_type_id = c.system_type_id)
Left outer join[%catalog%.] Sys.identity_columns i on (i.object_id = c.object_id andi.column_id = c.column_id)
Left outer join[%catalog%.] Sys.default_constraints D on (d.object_id = c.default_object_id)
where
O.type in (' U ', ' S ', ' V ')
[and U.name =%.q:owner%]
[and O.name=%.q:table%]
ORDER by 1, 2, 3

6, the front is ready to work, now start to generate models it:

A) Select Menu Database->reverseengineer Database (ctrl+r):

b) in the pop-up window, select the tab selection (this window is the default), select using a data source

c) Then click OK, in the Pop-up window select the need to import the database and select the table model to import (this can not be fully imported)

d) Last click on confirm to generate the

9. PD reverse engineering-from database to model (ER diagram)