The Will Will Web

記載著 Will 在網路世界的學習心得與技術分享

如何編輯 SQL Server 中物件的擴充屬性(Extended Property)

日前曾經介紹過一個好用的工具【推薦好用工具:Data Dictionary Creator】,這套工具就是透過寫入 SQL Server 中物件的擴充屬性(Extended Property)達成額外註解文件的目的,所有資訊全部都儲存在資料庫中,完全不需要額外儲存任何資料庫、表格、或欄位的 metadata 到任何其他資訊載體。

若你想透過 T-SQL 寫入擴充屬性的話,可以參考 sp_addextendedproperty 預儲程序,在 MSDN 尚有很詳細的說明,底下是一些操作的範例:

資料庫層級擴充屬性

  以下範例是將 Northwind 資料庫加上一個名為「負責人」的擴充屬性:

USE [master]
GO
EXEC [Northwind].sys.sp_addextendedproperty @name=N'負責人', @value=N'Will, 分機:8830' 
GO

  那麼你在 Management Studio 中的資料庫屬性的「��充屬性」頁籤就會看到「負責人」這個屬性:

  在 Management Studio 中的資料庫屬性的「擴充屬性」頁籤就會看到「負責人」這個屬性。

  若要更新此屬性,就要改用 sp_updateextendedproperty 預儲程序:

USE [master]
GO
EXEC [Northwind].sys.sp_updateextendedproperty @name=N'負責人', @value=N'Will, 分機:8831' 
GO

  若要刪除此屬性,就要改用 sp_dropextendedproperty 預儲程序,但這時不需要傳入 @value 參數:

USE [master]
GO
EXEC [Northwind].sys.sp_dropextendedproperty @name=N'負責人'
GO

表格層級擴充屬性

  表格層級就需要多傳入 Level 0 ( Schema Level ) 與 Level 1 ( Object Level ) 的參數,如下範例:

  新增擴充屬性

EXEC sys.sp_addextendedproperty 
@name=N'MS_Description', 
@value=N'儲存與地址相關的資訊' ,
@level0type=N'SCHEMA', 
@level0name=N'dbo', -- Schema Name
@level1type=N'TABLE', 
@level1name=N'Address' -- Table Name
GO

  而更新與刪除屬性就跟資料庫層級擴充屬性的用法一模一樣,記得要多指定 Level 0 與 Level 1 的參數即可。

欄位層級擴充屬性

  欄位層級就需要多傳入 Level 0 ( Schema Level ) 與 Level 1 ( Object Level ) 與 Level 2 ( Column Level ) 的參數了,例如:

EXEC sys.sp_addextendedproperty 
@name=N'MS_Description', 
@value=N'居住城市' ,
@level0type=N'SCHEMA', 
@level0name=N'dbo', -- Schema Name
@level1type=N'TABLE', 
@level1name=N'Address',-- Table Name 
@level2type=N'COLUMN', 
@level2name=N'City' -- Column Name
GO

其他像是 VIEW, Assembly, Index, Filegroup, ... 幾乎所有 SQL Server 內的物件都可以設定擴充屬性喔!