2015年12月8日 星期二

[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

沒有留言:

張貼留言