您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> SQL Server 2005

PowerDesigner 16 產生 SQL Server 2005/2008 欄位註釋腳本修改指南

編輯：SQL Server 2005

背景

PowerDesigner 逆向工程/產生資料庫腳本時，預設範本未包含欄位說明（MS_Description 擴充屬性），導致產出的 CREATE TABLE 腳本缺少 sp_addextendedproperty 呼叫。需自訂 DBMS 範本修正。

操作步驟

開啟 PowerDesigner → Tools → Resources → DBMS。
工具列點選 New → Copy from Template → 選擇 SQL Server 2005（或 2008 對應 sqlsv2k8.xdb）。
左側樹狀導覽：Script → Objects → Column → SqlListQuery。
將右側編輯區內容替換為下列修正腳本（關鍵新增兩行 p.value 取得擴充屬性）：

修正後 SqlListQuery 腳本


{OWNER, TABLE, S, COLUMN, DTTPCODE, LENGTH, SIZE, PREC, COMPUTE, NOTNULL, IDENTITY, DOMAIN, DEFAULT, ExtIdentitySeedInc, COMMENT, COLNNAME, ExtCollation, ExtIdtNotForReplication, ExtDeftConstName, ExtRowGuidCol}

SELECT
    u.name,
    o.name,
    c.column_id,
    c.name,
    CASE WHEN c.system_type_id IN (165,167,231) AND c.max_length = -1 THEN t.name + '(Max)' ELSE t.name END,
    c.precision,
    CASE c.max_length WHEN -1 THEN 0 ELSE CASE WHEN c.system_type_id IN (99,231,239) THEN c.max_length/2 ELSE c.max_length END END AS colnA,
    c.scale,
    CASE c.is_computed WHEN 1 THEN CONVERT(VARCHAR(8000), (SELECT z.definition FROM [%CATALOG%.]sys.computed_columns z WHERE z.object_id = c.object_id AND z.column_id = c.column_id)) ELSE '' END AS colnB,
    CASE c.is_nullable WHEN 1 THEN 'NULL' ELSE 'NOTNULL' END,
    CASE c.is_identity WHEN 1 THEN 'identity' ELSE '' END,
    CASE WHEN c.user_type_id <> c.system_type_id THEN (SELECT d.name FROM [%CATALOG%.]sys.types d WHERE d.user_type_id = c.user_type_id) ELSE '' END AS colnC,
    CONVERT(VARCHAR(8000), d.definition),
    CASE c.is_identity WHEN 1 THEN CONVERT(VARCHAR, i.seed_value) + ', ' + CONVERT(VARCHAR, i.increment_value) ELSE '' END AS colnD,
    CONVERT(VARCHAR(8000), ep.value),      -- ★ 新增：欄位說明 (MS_Description)
    CONVERT(VARCHAR(8000), ep.value),      -- ★ 新增：對應模板 COMMENT 欄位
    c.collation_name,
    CASE i.is_not_for_replication WHEN 1 THEN 'true' ELSE 'false' END,
    dc.name,
    CASE c.is_rowguidcol WHEN 1 THEN 'true' ELSE 'false' END
FROM [%CATALOG%.]sys.columns c
JOIN [%CATALOG%.]sys.objects o ON o.object_id = c.object_id
JOIN [%CATALOG%.]sys.schemas u ON u.schema_id = o.schema_id
JOIN [%CATALOG%.]sys.types t ON t.user_type_id = c.system_type_id
LEFT OUTER JOIN [%CATALOG%.]sys.identity_columns i ON i.object_id = c.object_id AND i.column_id = c.column_id
LEFT OUTER JOIN [%CATALOG%.]sys.default_constraints dc ON dc.object_id = c.default_object_id
LEFT OUTER JOIN [%CATALOG%.]sys.extended_properties ep ON ep.major_id = o.object_id AND ep.minor_id = c.column_id AND ep.class = 1
WHERE o.type IN ('U','S','V')
[ AND u.name = %.q:OWNER%]
[ AND o.name = %.q:TABLE%]
ORDER BY 1, 2, 3

關鍵修改點說明

SELECT 清單新增兩欄 ep.value：對應模板標記 COMMENT 與 COLNNAME（視具體模板定義），使產生腳本時包含 sp_addextendedproperty 'MS_Description', '說明文字', 'SCHEMA', 'dbo', 'TABLE', 'TableName', 'COLUMN', 'ColName'。
JOIN sys.extended_properties：class = 1 代表 COLUMN 級屬性，major_id = object_id、minor_id = column_id。
其餘欄位保持原樣，確保相容原有產生邏輯（型別、身分識別種子/增量、預設約束名稱、RowGUID、Collation 等）。