Log in

PD Series - Customize comments for tables

Customize DDL for table/column comments in PowerDesigner

In several projects, FastChangeCo's data modelers on the Data Management Center of Excellence (DMCE) team had an issue with the way PowerDesigner generates comments for tables and columns for the SQL Server database. Xuefang Kaya (one of the data modelers on the team), asked about the problems, says to the DMCE team:

"No, it's not that PowerDesigner refuses to generate the comments at all. It's more that PowerDesigner is generating the wrong DDL for the SQL Server."

What is the problem?

Xuefang's research into the problem shows the following:

When a schema is defined in the general table properties tab, PowerDesigner generates the following DDL to add a table comment — the DDL for a column comment is similar.

execute sp_addextendedproperty 'MS_Description',
'This is a column comment',
'user', 'Playground', 'table', 'MyCommentTable'

When this query is executed in SQL Server Management Studio, you get an error message:

Msg 15135, Level 16, State 1, Procedure sp_addextendedproperty, Line 37 [Batch Start Line 0]
Object is invalid. Extended properties are not permitted on 'Playground.MyCommentTable', or the object does not exist.

Why does the query give this error message? Because ‘Playground’ is not a user, but a schema! The user ‘Playground’ does not exist in the sample database. So the error message is correct.

By the way, Microsoft does not recommend to specify a user when applying an extended property to a database object! This can lead to ambiguities in name resolution [1].

Schema vs. User

We do not recommend specifying USER as a level 0 type when you apply an extended property to a database object, because this can cause name resolution ambiguity. For example, assume user Mary owns two schemas (Mary and MySchema) and these schemas both contain a table named MyTable. If Mary adds an extended property to table MyTable and specifies @level0type = N'USER', @level0name = Mary, it is not clear to which table the extended property is applied. To maintain backward compatibility, SQL Server will apply the property to the table that is contained in the schema named Mary.

So, to avoid a lot of other problems, creating the missing user is probably not the best solution, Xuefang thinks.

What is the solution?

Xuefang knows that PowerDesigner provides the possibility to edit the current DBMS properties. With this built-in feature it is possible to fix the described problem.

While PowerDesigner does not allow to modify the original RDBMS files, it is necessary to copy the existing RDBMS resource file to another location [2].

The resource files provided with PowerDesigner inside the Program Files folder cannot be modified directly. To create a copy for editing, use the New tool on the resource file list, and save it in another location. To include resource files from different locations for use in your models, use the Path tool on the resource file list.

The RDMBS resource files contain all the information for PowerDesigner to generate the DDL, DML, and SQL. For many databases there are RDBMS resource files in different versions.

Read the article Creating a customized DBMS resource file from the PD series.

The newly created or already existing RDBMS resource file can now be modified. For this purpose Xuefang opens the DBMS Properties dialog via the Database -> Edit Current DBMS... menu. 

Xuefang searches and finds the right places in the RDBMS resource file to make the adjustment to the DDL for the extendedproperty for tables, columns and views (clicking on the images opens them):

 Table comment

 Causes error:

Issue in script TableComment object of the DBMS.


Fixed: Issue in script TableComment object of the DBMS.

 Table-Column comment

 Causes error:

Issue in script ColumnComment object of the DBMS.


Fixed: Issue in script ColumnComment object of the DBMS.

 View comment

 Causes error:

Issue in script ViewComment object of the DBMS.


Fixed: Issue in script ViewComment object of the DBMS.

After all the tests have been successfully completed, the DMCE team is excited to have found a simple solution to an annoying inconvenience at the end.

The DMCE team is so stoked with this approach that they would like to fix more of the problems, inconveniences and bugs with PowerDesigner this way.

But that's another story. More about this in the next article of the series. You definitely have to stop by again.

So long
Yours Dirk

The previous article in the PowerDesigner series is: PD Series - Change default value
Back to the main article of the PowerDesigner series: PowerDesigner Series

[1] https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-addextendedproperty-transact-sql
[2] PowerDesigner Documentation (v16.6 SP04, Chapter DBMS Definition Files)

Like what you see?

Hit the buttons below to follow us, you won't regret it...