日前曾經介紹過一個好用的工具【推薦好用工具: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 中的資料庫屬性的「擴充屬性」頁籤就會看到「負責人」這個屬性:
若要更新此屬性,就要改用 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 物件都可以設定擴充屬性喔!👍