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."
Customize DDL for table/column comments in PowerDesigner
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'
go
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:
Fixed:
Table-Column comment
Causes error:
Fixed:
View comment
Causes error:
Fixed:
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: Change default value
The next article in the PowerDesigner series is: PowerDesigner on Apple Mac Silicon
Back to 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)