[SP] 修改資料表描述(sp_addextendedproperty,sp_updateextendedproperty)

這篇又是瘋狂開發後，補丁的一篇文章

身為華人，只能寫英文的欄位名稱實在很難懂

尤其是像英文像我這麼爛的人

透過描述加上中文說明，比較清楚欄位用途，文件也會好寫得多

如果使用Microsoft SQL Server Management Studio 的工具來建立資料表

可以很簡單的透過UI來設定



畫面如下：

但是在瘋狂的建立一堆資料表後才要補丁

用上面的方法可能要修改到天荒地老

只要重複十次以上我可能就要起笑了

或是使用create語法建立資料表時

用MS SQL內建的sp_addextendedproperty, sp_updateextendedproperty可以快速新增描述

當還沒加描述屬性時，用sp_addextendedproperty增加

範例如下：

exec sp_addextendedproperty 'MS_Description', '員工代碼', 'SCHEMA', 'dbo', 'table', 'Employees', 'COLUMN','EmployeeID'

還沒增加屬性前一定要用sp_addextendedproperty

否則會出現下列錯誤訊息

Property cannot be updated or deleted. Property 'MS_Description' does not exist for 'dbo.Employees.EmployeeID'.

當已經加上描述屬性，想要修改時，使用sp_updateextendedproperty修改

exec sp_updateextendedproperty 'MS_Description', '員工代碼', 'SCHEMA', 'dbo', 'table', 'Employees', 'COLUMN','EmployeeID'

否則會出現下列錯誤訊息

Property cannot be added. Property 'MS_Description' already exists for 'dbo.Employees.EmployeeID

沒有描述屬性 → sp_addextendedproperty

已有描述屬性 → sp_updateextendedproperty

根據這個規則可以建立Stored Procedure如下

CREATE PROCEDURE [dbo].[sp_ModifyColumDesc]
 @tableName varchar(50),@colName varchar(50), @colDesc nvarchar(50)
as
IF not exists(SELECT * FROM ::fn_listextendedproperty (N'MS_Description', N'SCHEMA', N'dbo', N'table', @tableName, N'COLUMN',@colName))
 BEGIN
  EXEC sp_addextendedproperty N'MS_Description', @colDesc, N'SCHEMA', N'dbo', N'table', @tableName, N'COLUMN',@colName
    END
ELSE
 BEGIN
  exec sp_updateextendedproperty N'MS_Description', @colDesc, N'SCHEMA', N'dbo', N'table', @tableName, N'COLUMN',@colName
    END
GO

執行方式為：

exec sp_ModifyColumDesc 'Employees','EmployeeID',N'員工編號'

其中fn_listextendedproperty用來取得MS_Description屬性

SELECT * FROM ::fn_listextendedproperty (N'MS_Description', N'SCHEMA', N'dbo', N'table', 'Employees', N'COLUMN','EmployeeID')

當第一個參數為NULL時，表示顯示所有取得該資料行的所有屬性說明

SELECT * FROM ::fn_listextendedproperty (NULL, N'SCHEMA', N'dbo', N'table', 'Employees', N'COLUMN','EmployeeID')

這樣就可以簡單的透過Excel整理資料欄位說明

exec sp_ModifyColumDesc 'Employees','EmployeeID',N'員工編號'
exec sp_ModifyColumDesc 'Employees','LastName',N'姓'
exec sp_ModifyColumDesc 'Employees','FirstName',N'名'
exec sp_ModifyColumDesc 'Employees','Title',N'職稱'
exec sp_ModifyColumDesc 'Employees','TitleOfCourtesy',N'稱謂'
exec sp_ModifyColumDesc 'Employees','BirthDate',N'生日'
exec sp_ModifyColumDesc 'Employees','HireDate',N'到職日'
exec sp_ModifyColumDesc 'Employees','Address',N'地址'
exec sp_ModifyColumDesc 'Employees','City',N'城市'
exec sp_ModifyColumDesc 'Employees','Region',N'地區'
exec sp_ModifyColumDesc 'Employees','PostalCode',N'郵遞區號'
exec sp_ModifyColumDesc 'Employees','Country',N'國家'
exec sp_ModifyColumDesc 'Employees','HomePhone',N'戶籍電話'
exec sp_ModifyColumDesc 'Employees','Extension',N'分機'
exec sp_ModifyColumDesc 'Employees','Photo',N'照片'
exec sp_ModifyColumDesc 'Employees','Notes',N'備註'
exec sp_ModifyColumDesc 'Employees','PhotoPath',N'相片位置'

原始碼下載: sp_ModifyColumDesc.sql