Use powerdesigner 15 to generate SQL Server Chinese comments

Problem description

After you create an SQL Server database using an SQL script generated by Power Designer (PD), the description of the table and field becomes garbled, such ????, After Google has published some related articles, there is no good solution, either for MySQL, Oracle, or SQL Server.

Solution

Start to think that is generated by the PD script file encoding is not correct, modify the encoding to simplified Chinese gb2312 can not; modify the database collation for Chinese-PRC-CI-AS is not good. Later, I directly created a test table on SQL Server, added comments, and exported scripts as follows:

Note generated by PD:

Execute sp_addextendedproperty 'Ms _ description ',

'Attachment ',

'User', 'dbo', 'table', 'wf _ attchment'

Go

 

Execute sp_addextendedproperty 'Ms _ description ',

'Attachment id ',

'User', 'dbo', 'table', 'wf _ attchment ', 'column', 'attchmentid'

Go

Notes generated by SQL Server:

Exec sys. sp_addextendedproperty @ name = n' MS _ description', @ value = n' attachment id', @ level0type = n' schema ', @ level0name = n' dbo ', @ level1type = n' table ', @ level1name = n' WF _ attchment', @ level2type = n' column ', @ level2name = n' attchmentid'

Go

 

After comparing the script generated by PD with the script exported by SQL Server, it is found that N is attached to the value assigned to the comment. After finding the root cause of the problem, it is easy to handle it. We need to handle it in the PD.

1. Modify the language attribute of the database model in PD | database | gernate datebase | format and change the language to UTF-8, as shown in:

2. Modify the annotation of the generated script. The modification is as follows:

Tools --> resources --> DBMS --> SQL Server 2008 open the DBMS Properties window, select Microsoft SQL Server 2008 on the General tab --> script --> objects, you can modify the table comment-Table comment and column comment-field comment in objects,

 

/Table comment/

/Default table comment/

[% Owner %? [. O: [execute] [exec] sp_addextendedproperty [% R %? [N] 'Ms _ description ',

[% R %? [N] %. Q: Comment %,

[% R %? [N] 'user', [% R %? [N] %. Q: Owner %, [% R %? [N] 'table', [% R %? [N] %. Q: Table %

: Declare @ currentuser sysname

Select @ currentuser = user_name ()

[. O: [execute] [exec] sp_addextendedproperty [% R %? [N] 'Ms _ description ',

[% R %? [N] %. Q: Comment %,

[% R %? [N] 'user', [% R %? [N] @ currentuser, [% R %? [N] 'table', [% R %? [N] %. Q: Table %

]

/Comment on the modified table/

[% Owner %? [. O: [execute] [exec] sp_addextendedproperty [% R %? [N] 'Ms _ description ',

[[N] %. Q: Comment %,

[% R %? [N] 'user', [% R %? [N] %. Q: Owner %, [% R %? [N] 'table', [% R %? [N] %. Q: Table %

: Declare @ currentuser sysname

Select @ currentuser = user_name ()

[. O: [execute] [exec] sp_addextendedproperty [% R %? [N] 'Ms _ description ',

[[N] %. Q: Comment %,

[% R %? [N] 'user', [% R %? [N] @ currentuser, [% R %? [N] 'table', [% R %? [N] %. Q: Table %

]

////////////////////////

/Field comment/

 

/Default field comment/

[% Owner %? [. O: [execute] [exec] sp_addextendedproperty [% R %? [N] 'Ms _ description ',

[% R %? [N] %. Q: Comment %,

[% R %? [N] 'user', [% R %? [N] %. Q: Owner %, [% R %? [N] 'table', [% R %? [N] %. Q: Table %, [% R %? [N] 'column', [% R %? [N] %. Q: column %

: Declare @ currentuser sysname

Select @ currentuser = user_name ()

[. O: [execute] [exec] sp_addextendedproperty [% R %? [N] 'Ms _ description ',

[% R %? [N] %. Q: Comment %,

[% R %? [N] 'user', [% R %? [N] @ currentuser, [% R %? [N] 'table', [% R %? [N] %. Q: Table %, [% R %? [N] 'column', [% R %? [N] %. Q: column %

]

/Comment the modified field/

[% Owner %? [. O: [execute] [exec] sp_addextendedproperty [% R %? [N] 'Ms _ description ',

[[N] %. Q: Comment %,

[% R %? [N] 'user', [% R %? [N] %. Q: Owner %, [% R %? [N] 'table', [% R %? [N] %. Q: Table %, [% R %? [N] 'column', [% R %? [N] %. Q: column %

: Declare @ currentuser sysname

Select @ currentuser = user_name ()

[. O: [execute] [exec] sp_addextendedproperty [% R %? [N] 'Ms _ description ',

[[N] %. Q: Comment %,

[% R %? [N] 'user', [% R %? [N] @ currentuser, [% R %? [N] 'table', [% R %? [N] %. Q: Table %, [% R %? [N] 'column', [% R %? [N] %. Q: column %

]

You can also modify the field annotation as follows:

/Comment the modified field/

[% Owner %? [. O: [execute] [exec] sp_addextendedproperty [% R %? [N] 'Ms _ description ',

[[N] %. Q: Comment %,

[% R %? [N] 'scheme', [% R %? [N] %. Q: Owner %, [% R %? [N] 'table', [% R %? [N] %. Q: Table %, [% R %? [N] 'column', [% R %? [N] %. Q: column %

: Declare @ currentuser sysname

Select @ currentuser = user_name ()

[. O: [execute] [exec] sp_addextendedproperty [% R %? [N] 'Ms _ description ',

[[N] %. Q: Comment %,

[% R %? [N] 'scheme', [% R %? [N] @ currentuser, [% R %? [N] 'table', [% R %? [N] %. Q: Table %, [% R %? [N] 'column', [% R %? [N] %. Q: column %

]