Powerdesigner10与sqlserver2000和sqlserver2005

rel="File-List" href="file:///D:%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml"> rel="themeData" href="file:///D:%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx"> rel="colorSchemeMapping" href="file:///D:%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml">

Powerdesigner10生成sqlserver注释

Powerdesigner10反向工程sqlserver注释

                      

准备

1.     创建名为Microsoft SQL Server 2005的DBMS

在（tools-Resource-DBMS）中执行New操作命令，新建DBMS时复制现有的DBMS(Microsoft SQL Server 2000),并将其命名为Microsoft SQL Server 2005

2.     为生成注释准备
    在（tools-Resource-DBMS）中选择Microsoft SQL Server 2000

DBMS Properties窗口中找到：Script-Object-Column

在Column 下建立Text键ColumnComment，将其Value设置为

EXECUTE sp_addextendedproperty N'MS_Description', N'%COMMENT%', N'user', N'%OWNER%', N'table', N'%TABLE%', N'column', N'%COLUMN%'

 

或者

EXECUTE sp_addextendedproperty  N'MS_Description', N'%COMMENT%', N'user', N'dbo', N'table', N'%TABLE%', N'column', N'%COLUMN%'

 

在（tools-Resource-DBMS）中选择Microsoft SQL Server 2005

DBMS Properties窗口中找到：Script-Object-Column

在Column 下建立Text键ColumnComment，将其Value设置为

EXECUTE sp_addextendedproperty N'MS_Description', N'%COMMENT%', N'user', N'%OWNER%', N'table', N'%TABLE%', N'column', N'%COLUMN%'

 

或者

EXECUTE sp_addextendedproperty  N'MS_Description', N'%COMMENT%', N'user', N'dbo', N'table', N'%TABLE%', N'column', N'%COLUMN%'

3.     为反向工程时，能够显示注释准备

在（tools-Resource-DBMS）中选择Microsoft SQL Server 2000

DBMS Properties窗口中找到：Script-Object-Column 在Column下找到 SqlListQuery键，把下面的代码复制到DBMS Properties窗口右边的Value文本框中。

{OWNER, TABLE, S, COLUMN, DTTPCODE, LENGTH, SIZE, PREC, COMPUTE, NOTNULL, IDENTITY, DOMAIN, DEFAULT, COMMENT, ExtIdentityIncrement, ExtIdentitySeed}

select
u.name,
o.name,
c.colid,
c.name,
case when (s.usertype < 100) then s.name else x.name end,
c.prec,
c.length,
c.scale,
z.text ,
case (c.status & 8) when 8 then 'NULL' else 'NOTNULL' end,
case (c.status & 128) when 128 then 'identity' else '' end,
case when (s.usertype < 100) then '' else s.name end,
v.text,
CONVERT(nvarchar, ISNULL(p.[value], '')) AS text,
case (c.status & 128) when 128 then ident_incr(u.name + '.' + o.name) else null end,
case (c.status & 128) when 128 then ident_seed(u.name + '.' + o.name) else null end
from
dbo.sysusers u
join dbo.sysobjects o on (o.uid = u.uid and o.type in ('U', 'S', 'V'))
join dbo.syscolumns c on (c.id = o.id)
left outer join sysproperties p on c.id = p.id and c.colid = p.smallid
left outer join dbo.systypes s on (c.usertype = s.usertype and s.xtype = c.xtype and c.usertype >= 0)
left outer join dbo.systypes x on (s.usertype > 100 and s.xtype = x.xtype and x.usertype not in (0, 18, 80) and x.usertype < 100)
left outer join dbo.syscomments z on (z.id = o.id and z.number = c.colid)
left outer join dbo.sysobjects d on (d.id = c.cdefault and d.category <> 0)
left outer join dbo.syscomments v on (v.id = d.id)
where 1 = 1
[ and u.name = %.q:OWNER%]
[ and o.name=%.q:TABLE%]
order by 1, 2, 3

在（tools-Resource-DBMS）中选择Microsoft SQL Server 2005

DBMS Properties窗口中找到：Script-Object-Column 在Column下找到 SqlListQuery键，把下面的代码复制到DBMS Properties窗口右边的Value文本框中。

{OWNER, TABLE, S, COLUMN, DTTPCODE, LENGTH, SIZE, PREC, COMPUTE, NOTNULL, IDENTITY, DOMAIN, DEFAULT, COMMENT, ExtIdentityIncrement, ExtIdentitySeed}

select

u.name,

o.name,

c.colid,

c.name,

case when (s.usertype < 100) then s.name else x.name end,

c.prec,

c.length,

c.scale,

z.text ,

case (c.status & 8) when 8 then 'NULL' else 'NOTNULL' end,

case (c.status & 128) when 128 then 'identity' else '' end,

case when (s.usertype < 100) then '' else s.name end,

v.text,

CONVERT(nvarchar, ISNULL(p.[value], '')) AS text,

case (c.status & 128) when 128 then ident_incr(u.name + '.' + o.name) else null end,

case (c.status & 128) when 128 then ident_seed(u.name + '.' + o.name) else null end

from

dbo.sysusers u

join dbo.sysobjects o on (o.uid = u.uid and o.type in ('U', 'S', 'V'))

join dbo.syscolumns c on (c.id = o.id)

left outer join sys.extended_properties p on c.id = p.major_id and c.colid = p.minor_id

left outer join dbo.systypes s on (c.usertype = s.usertype and s.xtype = c.xtype and c.usertype >= 0)

left outer join dbo.systypes x on (s.usertype > 100 and s.xtype = x.xtype and x.usertype not in (0, 18, 80) and x.usertype < 100)

left outer join dbo.syscomments z on (z.id = o.id and z.number = c.colid)

left outer join dbo.sysobjects d on (d.id = c.cdefault and d.category <> 0)

left outer join dbo.syscomments v on (v.id = d.id)

where 1 = 1

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

[ and o.name=%.q:TABLE%]

order by 1, 2, 3

使用powerdesigner

1.     生成数据库

进入File-New，在左边的列表中选择PhysicalDataModel，如果目标数据库为sqlserver2000则选择DBMS为Microsoft SQL Server 2000,同理sqlserver2005则选择DBMS为Microsoft SQL Server 2005

创建数据库，创建表，创建列，创建用户(省略)

生成数据库脚本：

选择ｄａｔａｂａｓｅ－generate database即可

2.     反向工程

进入File-New，在左边的列表中选择PhysicalDataModel，如果目标数据库为sqlserver2000则选择DBMS为Microsoft SQL Server 2000,同理sqlserver2005则选择DBMS为Microsoft SQL Server 2005

选择Database->configure connections,转到system dsn标签，点击"添加",选择驱动程序

在data source name 中,可以随便命名一个"ora-test",在tns-server name中选择第一步中的服务名称:mylcl.点击"test connection",输入用户名密码,connection ok!

点击database->reverse engineer database ,选择odbc datasource:ora-test.然后点击确定