这段时间准备离职在整理自己手头的项目,老大说要整理数据库(说明一下数据库是我之前设计的),由于数据库表比较多在数据库里也不好看,所以要建模型,我就想呀要是手工去写多累,身为程序员我怎么会去做那么重复性工作,数据库一开始就是PD建的模,不过后来添加表改字段比较多现在PD模型与数据库基本不一致,还好的是平时把改的字段说明都得清楚,于是乎就有了下面的内容:
说了那么多废话还是直奔主题吧,这里说的是PD逆向工程当然就是由数据库转为模型了,下面是步骤:
1、 在控制面板-》管理工具-》数据源（ODBC）-》用户DSN –》用户数据源下添加一个数据源（这里根据情况添加数据源）
图中sql2008就是我添加的数据源，这里因为我的数据库所在数据源是SQL server2008的。
2、 打开PowerDesigner新建PDM模型如图：
3、选择Database下的Connect..
4、DataSource选择ODBCmachine data source,下面选择我们第一部建立的数据源sql2008，然后在login输入登录数据库的用户名和密码
5、设置完连接的数据源接下来我们选择Database下的EditCurrent DBMS（这一步操作是为了在导入模型时将表的描述和字段的说明导入，我相信大家都希望看到的是模型显示描述而不是死板板字段或者表名）
6、修改模型数据导入语句,选择General->Microsoft SQL Server 2005->Script->Objects->Table->SqlListQuery
其实就是编辑导入数据模型和数据库对表对应关系，原来的是设置不能导入表描述所以做了如下修改,
系统默认代码：
{OWNER,TABLE, TABLE_TYPE, COMMENT}
select
u.name,
o.name,
case (o.type) when '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
修改后的代码：
{OWNER,TABLE, TABLE_TYPE, COMMENT,TNAME}
select
u.name,
o.name,
case (o.type) when '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_properties f on
f.major_id=o.id and f.minor_id=0 and f.class=1
where
o.type in ('U', 'S')
[ and u.name = %.q:OWNER%]
order by 1, 2
7、修改模型字段导入语句，选择General->Microsoft SQL Server 2005->Script->Objects ->Column->SqlListQuery
其实就是编辑导入数据模型和数据库字段对应关系，原来的是设置不能导入字段描述所以做了如下修改
系统默认代码：
{OWNER,TABLE, S, COLUMN, DTTPCODE, LENGTH, SIZE, PREC, COMPUTE, NOTNULL, IDENTITY,DOMAIN, DEFAULT, ExtIdentitySeedInc, COMMENT, ExtCollation,ExtIdtNotForReplication, ExtDeftConstName, ExtRowGuidCol}
select
u.name,
o.name,
c.column_id,
c.name,
case when 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 when c.system_type_id in (99, 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) when 1 then 'NULL' else'NOTNULL' end,
case(c.is_identity) when 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 '' end 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) when 1 then'true' else 'false' end,
d.name,
case(c.is_rowguidcol) when 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
修改后代码：
{OWNER,TABLE, S, COLUMN, DTTPCODE, LENGTH, SIZE, PREC, COMPUTE, NOTNULL, IDENTITY,DOMAIN, DEFAULT, ExtIdentitySeedInc, COMMENT,COLNNAME, ExtCollation,ExtIdtNotForReplication, ExtDeftConstName, ExtRowGuidCol}
select
u.name,
o.name,
c.column_id,
c.name,
case when 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 else case when c.system_type_id in (99, 231, 239) then (c.max_length/2) else(c.max_length) end end as colnA,
c.scale,
case(c.is_computed) when 1 then convert(varchar(8000),(select z.definition from [%CATALOG%.]sys.computed_columns z where z.object_id = c.object_id and z.column_id = c.column_id)) else '' end as colnB,
case(c.is_nullable) when 1 then 'NULL' else'NOTNULL' end,
case(c.is_identity) when 1 then 'identity'else '' end,
case when(c.user_type_id <>c.system_type_id) then (select d.name from [%CATALOG%.]sys.types d where d.user_type_id = c.user_type_id) else '' end as colnC,
convert(varchar(8000), d.definition),
case (c.is_identity) when 1 then convert(varchar, i.seed_value) + ', ' + convert(varchar, i.increment_value)else '' end as colnD,
(select convert(varchar(8000), value) from [%CATALOG%.]sys.extended_properties as ex where ex.major_id = c.object_id and ex.minor_id = c.column_id),
(select convert(varchar(8000), value) from [%CATALOG%.]sys.extended_properties as ex where ex.major_id = c.object_id and ex.minor_id = c.column_id),
c.collation_name,
case (i.is_not_for_replication) when 1 then'true' else 'false' end,
d.name,
case(c.is_rowguidcol) when 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 and i.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
8、前面都是准备工作，现在开始生成模型吧：
a) 选择菜单Database->ReverseEngineer Database…
b) 弹出的窗口中,选择选项卡Selection（默认就是这个窗口）,选择Using a data source
c) 然后点击确定，在弹出窗口选择需要导入数据库以及选择需要导入的表模型（这里可以不完全导入）
d)最后点击确认就生成了了，最后效果图如下：
2202
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
