How to enable PowerDesigner to automatically generate a corner book containing SQL Server 2000 tables and column comments

PowerDesigner is a famous product of Sybase. Since I started using 16-bit windows, I have been familiar with and used this tool. It should be said that I have deep feelings. PowerDesigner is a powerful tool designed by DBAs and software architects. With the continuous upgrade of the version, PowerDesigner provides more and more powerful functions. Starting with this article, we will introduce some tips in PowerDesinger to help you better and more efficiently use this CASE tool.

Question proposal

I have seen some friends on the Internet asking, in the Physical Model design of PowerDesigner, apart from Oracle, when selecting other database models and finally generating database creation scripts, the database script code for the record table and column comment information is not automatically generated.

Please allow me to explain the concept of this problem first. We use PowerDesigner to design the oracle database, and the final script can be as follows, the comment statement is the script statement I mentioned above to save the annotation information in oracle. After execution in oracle, you can obtain the information by querying the oracle Data dictionary. The advantage of doing so is that it is convenient to view the table structure in the future and you do not need to find it from the database documentation. It is of application significance for team development.

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

/* Table: EMP */

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

Create table EMP (

"EmpID" NUMBER,

"EmpName" VARCHAR2 (50 ),

Sex char (1)

)

/

Comment on table EMP is 'employee information'

/

Comment on column EMP. "EmpID" is 'employee number'

/

Comment on column EMP. "EmpName" is 'employee name'

/

Comment on column EMP. SEX is 'gender F: female M: Male'

/

SQL Server also has such statements. However, you often find that the database model selected is SQL Server. When the database creation script is automatically generated, even if you select the option to generate comment statements, powerdesigner won't automatically generate it for you. Does PowerDesigner not support SQL Server? The answer is obviously no.

Problem Solving

The PowerDesigner tool supports Model Design for any type of relational database, because the feature definitions of each database model in PowerDesigner are stored in an xdb file, we can find it in [PowerDesigner_Install_Home]/Resource Files/DBMS. Of course, we can also create a new xdb and define the features of the database model we want to support. The answer to this question is obvious.

Next, I will briefly describe how to use PowerDesinger9.5 Enterprise Edition. Some simplified versions of Sybase (such as SQL mod.pdf) cannot customize xdb. Please note that.

1. Choose TOOLS> RESOUCES> DBMS to enter the Custom Data Model. See figure 1.

Figure 1

2. The PowerDesigner dialog box is displayed. The list box lists

Database Model (figure 2 ). We choose to create a new one. Enter the name SQL server 2000 (EXTENDED) and select copy from the existing SQL server 2000, so that SQL SERVER 2000 (EXTENDED) it contains all the features of the original SQL SERVER2000 model (Figure 3 ).

Figure 2

Figure 3

3. Next, we need to add the feature that supports automatic generation of table and column comment code for SQL Server 2000 (Extended. PowerDesinger organizes the defined features in a tree structure (Figure 4). The job of this article is to define the features of Script code. We need to modify the Script feature code in Script \ Objects \ Table \ TableComment and Script \ Objects \ Column \ ColumnComment.

4 SQL Server 2000 saves table and column comments in the database by calling the system stored procedure sp_addextendedproperty. Then we only need to write them separately

The table-level annotation code is:
EXECUTE sp_addextendedproperty N 'Ms _ description', n' % COMMENT % ', n'user', n'dbo', n'table', n' % table %', NULL, NULL
The column-level comment code is
EXECUTE sp_addextendedproperty N 'Ms _ description', n' % COMMENT % ', n'user', n'dbo', n'table', n' % table % ', N 'column ', n' % column %'

Let's look at the code and immediately understand what is going on (figure 5 ). % COMMENT % is equivalent to replacing the variable. PowerDesigner automatically replaces the value of the variable in the design model when generating the script.

Figure 4

Figure 5

5. Save SQL Server 2000 (Extended) into an xdb file.

6. Now we can create a new Physical Data Model. Select SQL Server 2000 (Extended) as the database Model ). To create a table, you must specify a database user. Generally, dbo is used (figure 6 ). We can add comments to both tables and columns.

Figure 6

7. Generate a Database creation script. Choose Databases> Generate Database. In the dialog box shown in Figure 7, select the comment option. Click OK to get the final database creation script. Let's open it and see that PowerDesinger generates the corresponding code according to the rule we told it.

...

EXECUTE sp_addextendedproperty N 'Ms _ description', N 'employee information', N 'user', N 'dbo', N 'table', N 'Employee', NULL, NULL

Go

EXECUTE sp_addextendedproperty N 'Ms _ description', n' primary key ID, automatically adding ', n' user', n' dbo', n' table', n' Employee ', N 'column ', N 'employeeid'

Go

....

8. Run this corner in SQL Server 2000 and then you can see these notes through Enterprise Manager.