• 發行項

使用資料庫物件的擴充屬性

當使用擴充屬性時，您可以加入文字 (例如，描述性內容或指示性內容)、加入輸入遮罩以及加入格式規則作為資料庫或資料庫本身的物件屬性。例如，您可以將擴充屬性加入結構描述、結構描述的檢視或檢視中的資料行。因為擴充屬性是儲存在資料庫中，使讀取屬性的所有應用程式能夠以同樣的方式評估物件。這有助於強制系統中所有程式如何對待資料的一致性。

下列是可使用的擴充屬性：

  • 指定資料表、檢視或資料行的標題。應用程式就可以在顯示資料表、檢視或資料行資訊的使用者介面中使用相同的標題。

  • 指定資料行的輸入遮罩，讓應用程式在執行 Transact-SQL 陳述式之前可以先驗證資料。例如，郵遞區號或電話號碼資料行的所需格式可以在擴充屬性中指定。

  • 指定在資料行中顯示資料的格式化規則。

  • 記錄應用程式可向使用者顯示的特定資料庫物件之描述。例如，描述可用於資料字典應用程式或報告中。

  • 指定應顯示的資料行的大小及視窗位置。

[!附註]

擴充屬性不應用來隱藏物件的機密資訊。任何授與物件權限的使用者，將可以檢視該物件的擴充屬性。例如，如果您授與使用者資料表的 SELECT 權限，使用者將可以檢視該物件的擴充屬性。

定義擴充屬性

每個擴充屬性都擁有使用者自訂名稱與數值。擴充屬性的值是一個最多可包含 7,500 個位元組資料的 sql_variant 值。您可以將多個擴充屬性加入單一物件中。

為了指定擴充屬性，SQL Server 資料庫中的物件會分類為三個層級，0、1 和 2。層級 0 是最高的層級，定義為包含在資料庫範圍的物件。層級 1 物件包含在結構描述或使用者範圍中，層級 2 物件包含在層級 1 物件中。任一層級的物件都可定義擴充屬性。

一個層級中的物件之參考，必須具有擁有或包含它們的更高層級物件的完整名稱。例如，當您將擴充屬性加入資料表資料行 (層級 2)，您也必須指定包含該資料行的資料表名稱 (層級 1) 以及包含該資料表的結構描述 (層級 0)。

在下列範例中，擴充屬性值 'Minimum inventory quantity.' 加入了 Product 資料表中的 SafetyStockLevel 資料行，該資料表是包含在 Production 結構描述中。

USE AdventureWorks;
GO
EXEC sys.sp_addextendedproperty 
@name = N'MS_DescriptionExample', 
@value = N'Minimum inventory quantity.', 
@level0type = N'SCHEMA', @level0name = Production, 
@level1type = N'TABLE',  @level1name = Product,
@level2type = N'COLUMN', @level2name = SafetyStockLevel;
GO

將擴充屬性加入物件中

下表列出您可以加入擴充屬性的物件。其中列出有效的層級 0、層級 1 與層級 2 物件類型，以及加入、卸除或檢視擴充屬性的必要屬性。

彙總

層級

層級類型

加入或卸除擴充屬性的必要權限

檢視擴充屬性的必要權限

註解

0

SCHEMA 或 USER

 

 

應該使用 SCHEMA 而非 USER。請參閱本主題稍後的「結構描述與使用者」。

1

AGGREGATE

針對彙總 OBJECT 的 ALTER

針對 OBJECT 的任何權限

適用於使用者自訂 CLR 彙總函式。

非對稱金鑰

層級

層級類型

加入或卸除擴充屬性的必要權限

檢視擴充屬性的必要權限

0

ASYMMETRIC KEY

針對 CERTIFICATE 的 ALTER

針對 KEY 的任何權限

1

無

 

 

2

無

 

 

組件

層級

層級類型

加入或卸除擴充屬性的必要權限

檢視擴充屬性的必要權限

0

ASSEMBLY

針對 ASSEMBLY 的 ALTER

針對 ASSEMBLY 的任何權限

1

無

 

 

2

無

 

 

憑證

層級

層級類型

加入或卸除擴充屬性的必要權限

檢視擴充屬性的必要權限

0

CERTIFICATE

針對 CERTIFICATE 的 ALTER

針對 CERTIFICATE 的任何權限

1

無

 

 

2

無

 

 

合約 (Service Broker)

層級

層級類型

加入或卸除擴充屬性的必要權限

檢視擴充屬性的必要權限

0

CONTRACT

針對 CONTRACT 的 ALTER

針對 CONTRACT 的任何權限

1

無

 

 

2

無

 

 

資料庫

層級

層級類型

加入或卸除擴充屬性的必要權限

檢視擴充屬性的必要權限

註解

0

無

針對 DATABASE 的 ALTER

針對 DATABASE 的 CONTROL

或

針對 DATABASE 的 TAKE OWNERSHIP

或

針對 DATABASE 的 VIEW DEFINITION

或

針對 SERVER 的 ALTER ANY DATABASE

或

針對 SERVER 的 CREATE DATABASE

屬性適用於資料庫本身。

1

無

 

 

 

2

無

 

 

 

範例

在下列範例中，擴充屬性加入了 AdventureWorks 範例資料庫本身。

USE AdventureWorks;
GO
EXEC sys.sp_addextendedproperty 
@name = N'MS_DescriptionExample', 
@value = N'AdventureWorks Sample OLTP Database';
GO

預設值

層級

層級類型

加入或卸除擴充屬性的必要權限

檢視擴充屬性的必要權限

註解

0

SCHEMA 或 USER

 

 

 

1

DEFAULT

針對 SCHEMA 的 ALTER

HAS_DBACCESS = 1

僅因回溯相容性而提供。如需詳細資訊，請參閱＜SQL Server 2008 中已被取代的 Database Engine 功能＞。

2

無

 

 

 

事件通知

層級

層級類型

加入或卸除擴充屬性的必要權限

檢視擴充屬性的必要權限

註解

0

EVENT NOTIFICATION

ALTER ANY DATABASE EVENT NOTIFICATION ON DATABASE

事件通知的擁有者

或

針對資料庫的 CONTROL、ALTER、TAKE OWNERSHIP 或 VIEW DEFINITION

或

ALTER ANY DATABASE EVENT NOTIFICATION

適用於資料庫層級的事件通知，資料庫是這些通知的父項。

擴充屬性無法加入伺服器層級的事件通知。

1

無

 

 

 

2

無

 

 

 

檔案群組或檔案名稱

層級

層級類型

加入或卸除擴充屬性的必要權限

檢視擴充屬性的必要權限

0

FILEGROUP

針對 DATABASE 的 ALTER

HAS_DBACCESS = 1

1

LOGICAL FILE NAME

針對 DATABASE 的 ALTER

HAS_DBACCESS = 1

2

無

 

 

範例

在下列範例中，會將擴充屬性加入 PRIMARY 檔案群組中。

USE AdventureWorks;
GO
EXEC sys.sp_addextendedproperty 
@name = N'MS_DescriptionExample', 
@value = N'Primary filegroup for the AdventureWorks sample database.', 
@level0type = N'FILEGROUP', @level0name = [PRIMARY];
GO

在下列範例中，擴充屬性加入了 PRIMARY 檔案群組中的 AdventureWorks_Data 邏輯檔案名稱。

USE AdventureWorks;
GO
EXEC sys.sp_addextendedproperty 
@name = N'MS_DescriptionExample', 
@value = N'Primary data file for the AdventureWorks sample database.', 
@level0type = N'FILEGROUP', @level0name = [PRIMARY],
@level1type = N'Logical File Name', @level1name = AdventureWorks_Data;
GO

函數

層級

層級類型

加入或卸除擴充屬性的必要權限

檢視擴充屬性的必要權限

註解

0

SCHEMA 或 USER

 

 

應該使用 SCHEMA 而非 USER。請參閱本主題稍後的「結構描述與使用者」。

1

FUNCTION

針對 OBJECT 函數的 ALTER

針對 COLUMN 的任何權限

或

針對 OBJECT 的任何權限

適用於這些使用者定義函數：

  • CLR 純量

  • CLR 資料表值

  • Transact-SQL 純量

  • Transact-SQL 內嵌資料表值

  • Transact-SQL 資料表值

如果未指定層級 2 類型，該屬性適用於函數本身。

2

COLUMN

針對 OBJECT 函數的 ALTER

針對 COLUMN 的任何權限

或

針對 OBJECT 的任何權限

 

2

CONSTRAINT

針對 OBJECT 函數的 ALTER

針對 COLUMN 的任何權限

或

針對 OBJECT 的任何權限

 

2

PARAMETER

針對 OBJECT 函數的 ALTER

針對 COLUMN 的任何權限

或

針對 OBJECT 的任何權限

 

範例

在下列範例中，擴充屬性加入了 ufnGetStock 使用者定義函數。

USE AdventureWorks;
GO
EXEC sys.sp_addextendedproperty 
@name = N'MS_DescriptionExample', 
@value = N'Scalar function returning the quantity of inventory for a specified ProductID.', 
@level0type = N'SCHEMA', @level0name = [dbo],
@level1type = N'FUNCTION', @level1name = ufnGetStock;
GO

在下列範例中，擴充屬性加入了使用者自訂函數 ufnGetStock 所定義的 @ProductID 參數。

USE AdventureWorks;
GO
EXEC sys.sp_addextendedproperty 
@name = N'MS_DescriptionExample', 
@value = N'Input parameter. Enter a valid ProductID.', 
@level0type = N'SCHEMA', @level0name = [dbo],
@level1type = N'FUNCTION', @level1name = ufnGetStock,
@level2type = N'PARAMETER', @level2name ='@ProductID';
GO

訊息類型

層級

層級類型

加入或卸除擴充屬性的必要權限

檢視擴充屬性的必要權限

0

MESSAGE TYPE

針對 MESSAGE TYPE 的 ALTER

HAS_DBACCESS = 1

1

無

 

 

2

無

 

 

資料分割函數

層級

層級類型

加入或卸除擴充屬性的必要權限

檢視擴充屬性的必要權限

0

PARTITION FUNCTION

針對 DATABASE 的 ALTER ANY DATASPACE

HAS_DBACCESS = 1

1

無

 

 

2

無

 

 

資料分割結構描述

層級

層級類型

加入或卸除擴充屬性的必要權限

檢視擴充屬性的必要權限

0

PARTITION SCHEME

針對 DATABASE 的 ALTER ANY DATASPACE

HAS_DBACCESS = 1

1

無

 

 

2

無

 

 

計畫指南

層級

層級類型

加入或卸除擴充屬性的必要權限

檢視擴充屬性的必要權限

0

PLAN GUIDE

針對 DATABASE 的 ALTER ANY DATASPACE

針對 SQL 資料庫或 TEMPLATE 指南的 VIEW DEFINITION、ALTER、TAKE OWNERSHIP 或 CONTROL，以及針對 OBJECT 指南之物件的 VIEW DEFINITION、ALTER、TAKE OWNERSHIP 或 CONTROL

1

無

 

 

2

無

 

 

下列範例會將擴充屬性加入 TemplateGuide1 計畫指南中。

USE AdventureWorks;
GO
EXECUTE sp_addextendedproperty @name=N'PlanGuideGroup', @value = N'MyTest', 
@level0type = N'Plan Guide', @level0name = 'TemplateGuide1';

程序

層級

層級類型

加入或卸除擴充屬性的必要權限

檢視擴充屬性的必要權限

註解

0

SCHEMA 或 USER

 

 

應該使用 SCHEMA 而非 USER。請參閱本主題稍後的「結構描述與使用者」。

1

PROCEDURE

針對 OBJECT 程序的 ALTER

附註附註
只有系統管理員 (sysadmin) 固定伺服器角色的成員，才能夠將擴充屬性加入擴充預存程序中。

針對 OBJECT 的任何權限

適用於這些使用者自訂預存程序：

  • CLR

  • Transact-SQL

  • 複寫篩選

如果未指定層級 2 類型，該屬性適用於程序本身。

2

PARAMETER

針對 OBJECT 程序的 ALTER

針對 OBJECT 的任何權限

 

佇列 (Service Broker)

層級

層級類型

加入或卸除擴充屬性的必要權限

檢視擴充屬性的必要權限

註解

0

SCHEMA 或 USER

 

 

應該使用 SCHEMA 而非 USER。請參閱本主題稍後的「結構描述與使用者」。

1

QUEUE

針對 OBJECT 佇列的 ALTER

針對 OBJECT 的任何權限

 

2

EVENT NOTIFICATION

針對 OBJECT 佇列的 ALTER

事件通知的擁有者

或

針對佇列的 CONTROL、ALTER、TAKE OWNERSHIP 或 VIEW DEFINITION

或

ALTER ANY DATABASE EVENT NOTIFICATION

事件通知與佇列可以有不同的擁有者。

遠端服務繫結 (Service Broker)

層級

層級類型

加入或卸除擴充屬性的必要權限

檢視擴充屬性的必要權限

0

REMOTE SERVICE BINDING

針對 REMOTE SERVICE BINDING 的 ALTER

針對 REMOTE SERVICE BINDING 的任何權限

1

無

 

 

2

無

 

 

路由 (Service Broker)

層級

層級類型

加入或卸除擴充屬性的必要權限

檢視擴充屬性的必要權限

0

ROUTE

針對 REMOTE SERVICE BINDING 的 ALTER

針對 ROUTE 的任何權限

1

無

 

 

2

無

 

 

規則

層級

層級類型

加入或卸除擴充屬性的必要權限

檢視擴充屬性的必要權限

註解

0

SCHEMA 或 USER

 

 

 

1

RULE

針對 SCHEMA 的 ALTER

HAS_DBACCESS = 1

提供這個項目的目的，是為了與舊版相容。如需詳細資訊，請參閱＜SQL Server 2008 中已被取代的 Database Engine 功能＞。

2

無

 

 

 

SCHEMA 或 USER

層級

層級類型

加入或卸除擴充屬性的必要權限

檢視擴充屬性的必要權限

註解

0

SCHEMA 或 USER

針對 SCHEMA 的 ALTER 或針對 USER 的 ALTER

針對 APPLICATION ROLE 的任何權限

或

針對 ROLE 的任何權限

或

針對 USER 的任何權限

如果未指定層級 1 類型，該屬性適用於結構描述或使用者本身。

USER 可為下列各項之一：

  • 應用程式角色

  • 資料庫角色

  • SQL Server 使用者

  • Windows 群組

  • Windows 使用者

當您將擴充屬性加入使用者時，應該只能使用 USER。

重要注意事項重要事項
USER 作為定義於層級 1 或層級 2 類型物件上的層級 0 類型，將在未來的 SQL Server 版本中加以移除。請避免在新的開發工作中使用這項功能，並規劃修改目前使用這項功能的應用程式。請改用 SCHEMA 作為層級 0 類型。

1

非固定

 

 

 

2

非固定

 

 

 

服務

層級

層級類型

加入或卸除擴充屬性的必要權限

檢視擴充屬性的必要權限

0

SERVICE

針對 SERVICE 的 ALTER

針對 SERVICE 的 ALTER

或

針對 SERVICE 的 CONTROL

或

針對 SERVICE 的 TAKE OWNERSHIP

或

針對 SERVICE 的 VIEW DEFINITION

1

無

 

 

2

無

 

 

對稱金鑰

層級

層級類型

加入或卸除擴充屬性的必要權限

檢視擴充屬性的必要權限

0

SYMMETRIC KEY

針對 CERTIFICATE 的 ALTER

針對 KEY 的任何權限

1

無

 

 

2

無

 

 

同義字

層級

層級類型

加入或卸除擴充屬性的必要權限

檢視擴充屬性的必要權限

註解

0

SCHEMA 或 USER

 

 

應該使用 SCHEMA 而非 USER。請參閱本主題稍後的「結構描述與使用者」。

1

SYNONYM

針對 SYNONYM 的 CONTROL

針對 OBJECT 的任何權限

 

2

無

 

 

 

資料表

層級

層級類型

加入或卸除擴充屬性的必要權限

檢視擴充屬性的必要權限

註解

0

SCHEMA 或 USER

 

 

應該使用 SCHEMA 而非 USER。請參閱本主題稍後的「結構描述與使用者」。

1

TABLE

針對 OBJECT 資料表的 ALTER

針對 COLUMN 的任何權限

或

針對 OBJECT 的任何權限

適用於使用者自訂資料表。如果未指定層級 2 類型，該屬性適用於資料表本身。

2

COLUMN

針對 OBJECT 資料表的 ALTER

針對 COLUMN 的任何權限

或

針對 OBJECT 的任何權限

 

2

CONSTRAINT

針對 OBJECT 資料表的 ALTER

針對 OBJECT 的 ALTER

或

針對 OBJECT 的 CONTROL

或

針對 OBJECT 的 TAKE OWNERSHIP

或

針對 OBJECT 的 VIEW DEFINITION

適用於這些條件約束：

  • CHECK

  • DEFAULT

  • FOREIGN KEY

  • PRIMARY KEY

  • UNIQUE

2

INDEX

針對 OBJECT 資料表的 ALTER

針對 COLUMN 的任何權限

或

針對 OBJECT 的任何權限

適用於關聯式和 XML 索引。

2

TRIGGER

針對 OBJECT 資料表的 ALTER

針對 OBJECT 的 ALTER

或

針對 OBJECT 的 CONTROL

或

針對 OBJECT 的 TAKE OWNERSHIP

或

針對 OBJECT 的 VIEW DEFINITION

適用於這些 DML 觸發程序：

  • CLR

  • Transact-SQL

不適用於 DDL 觸發程序。

範例

在下列範例中，擴充屬性加入了 Person 結構描述中的 Address 資料表。

USE AdventureWorks;
GO
EXEC sys.sp_addextendedproperty 
@name = N'MS_Description', 
@value = N'Street address information for customers, employees, and vendors.', 
@level0type = N'SCHEMA', @level0name = Person, 
@level1type = N'TABLE',  @level1name = Address;
GO

在下列範例中，擴充屬性加入了 Person 結構描述中 Address 資料表的 IX_Address_StateProviceID 索引。

USE AdventureWorks;
GO
EXEC sys.sp_addextendedproperty 
@name = N'MS_DescriptionExample', 
@value = N'Nonclustered index on StateProvinceID.', 
@level0type = N'SCHEMA', @level0name = Person, 
@level1type = N'TABLE',  @level1name = Address,
@level2type = N'INDEX',  @level2name = IX_Address_StateProvinceID;
GO

觸發程序

層級

層級類型

加入或卸除擴充屬性的必要權限

檢視擴充屬性的必要權限

註解

0

TRIGGER

針對 DATABASE 的 ALTER ANY TRIGGER

針對 DATABASE 的 ALTER ANY TRIGGER

只適用於 DDL 觸發程序。

1

無

 

 

 

2

無

 

 

 

類型

層級

層級類型

加入或卸除擴充屬性的必要權限

檢視擴充屬性的必要權限

註解

0

TYPE

針對 TYPE 的 CONTROL

針對 TYPE 的任何權限

適用於舊版 SQL Server 中建立的使用者自訂類型。

提供這個項目的目的，是為了與舊版相容。如需詳細資訊，請參閱＜SQL Server 2008 中已被取代的 Database Engine 功能＞。

1

無

 

 

 

2

無

 

 

 

類型

層級

層級類型

加入或卸除擴充屬性的必要權限

檢視擴充屬性的必要權限

註解

0

SCHEMA 或 USER

 

 

應該使用 SCHEMA 而非 USER。請參閱本主題稍後的「結構描述與使用者」。

1

TYPE

針對 TYPE 的 CONTROL

針對 TYPE 的任何權限

 

2

無

 

 

 

檢視

層級

層級類型

加入或卸除擴充屬性的必要權限

檢視擴充屬性的必要權限

註解

0

SCHEMA 或 USER

 

 

應該使用 SCHEMA 而非 USER。請參閱本主題稍後的「結構描述與使用者」。

1

VIEW

針對 OBJECT 檢視的 ALTER

針對 COLUMN 的任何權限

或

針對 OBJECT 的任何權限

適用於使用者自訂檢視。如果未指定層級 2 類型，該屬性適用於檢視本身。

2

COLUMN

針對 OBJECT 檢視的 ALTER

針對 COLUMN 的任何權限

或

針對 OBJECT 的任何權限

 

2

INDEX

針對 OBJECT 檢視的 ALTER

針對 COLUMN 的任何權限

或

針對 OBJECT 的任何權限

適用於關聯式和 XML 索引。

2

TRIGGER

針對 OBJECT 檢視的 ALTER

針對 DATABASE 的 ALTER ANY TRIGGER

或

針對 OBJECT 的 ALTER

或

針對 OBJECT 的 CONTROL

或

針對 OBJECT 的 TAKE OWNERSHIP

或

針對 OBJECT 的 VIEW DEFINITION

適用於這些 DML 觸發程序：

  • CLR

  • Transact-SQL

不適用於 DDL 觸發程序。

XML 結構描述集合

層級

層級類型

加入或卸除擴充屬性的必要權限

檢視擴充屬性的必要權限

註解

0

SCHEMA 或 USER

 

 

應該使用 SCHEMA 而非 USER。請參閱本主題稍後的「結構描述與使用者」。

1

XML SCHEMA COLLECTION

針對 XML SCHEMA COLLECTION 的 ALTER

針對 XML SCHEMA COLLECTION 的 ALTER

或

針對 XML SCHEMA COLLECTION 的 CONTROL

或

針對 XML SCHEMA COLLECTION 的 REFERENCES

或

針對 XML SCHEMA COLLECTION 的 TAKE OWNERSHIP

或

針對 XML SCHEMA COLLECTION 的 VIEW DEFINITION

擴充屬性無法加入 XML 命名空間。

2

無

 

 

 

沒有擴充屬性支援的物件

無法在下列物件上定義擴充屬性：

  • 未列於前表中的資料庫範圍物件。這包含全文檢索物件。

  • 在資料庫範圍之外的物件，例如 HTTP 端點。

  • 未命名的物件，例如資料分割函數參數。

  • 憑證、對稱金鑰、非對稱金鑰以及認證。

  • 系統定義的物件，例如系統資料表、目錄檢視以及系統預存程序。

結構描述與使用者

在舊版 SQL Server 中，使用者擁有資料表、檢視及觸發程序這類資料庫物件。因此，將擴充屬性加入其中一個這些物件，並指定使用者名稱作為允許的層級 0 類型。資料庫物件現在是包含在與擁有結構描述的使用者不相關的結構描述中。

當您將擴充屬性套用至資料庫物件時，若將 USER 指定為層級 0 類型，它可能會造成名稱解析混亂。例如，假設使用者 Mary 擁有 Mary 和 MySchema 兩個結構描述，而這兩個結構描述都包含一個名為 MyTable 的資料表。如果 Mary 在 MyTable 資料表中加入擴充屬性，並指定 @level0type = N'USER'、@level0name = Mary，就無法清楚辨認是哪一個資料表套用了擴充屬性。為了維持回溯相容性，SQL Server 將套用屬性至包含在名為 Mary 之結構描述中的資料表。如需有關使用者和結構描述的詳細資訊，請參閱＜使用者結構描述分隔＞。

使用輸入遮罩與 CHECK 條件約束

CHECK 條件約束與提供輸入遮罩的擴充屬性，都可用來指定資料表或檢視資料行必須有的資料模式。大部分的站台都會選擇其中一個，除非下列其中一項發生：

  • CHECK 條件約束被視為暫時的方法，直到處理這個資料表的所有程式可以變更為使用輸入遮罩屬性為止。

  • 站台亦支援使用者透過不需讀取擴充屬性的臨時性工具來更新資料。

CHECK 條件約束上的輸入遮罩之優點是在應用程式中套用邏輯。如果使用者所提供的資料未正確格式化，可能會產生提供更詳細資訊的錯誤。輸入遮罩的缺點是需要個別呼叫 fn_listextendedproperty 或 sys.extended_properties 以取得屬性，而且必須在所有的程式中加入強制遮罩的邏輯。

複寫擴充屬性

只在發行者與訂閱者之間的初始同步處理中複寫擴充屬性。如果您在初始同步處理之後加入或修改擴充屬性，就不會複寫這項變更。如需有關複寫資料庫物件的詳細資訊，請參閱＜發行資料和資料庫物件＞。

在應用程式中使用擴充屬性

擴充屬性只會提供一個儲存資料的具名位置。所有的應用程式都必須設定為能查詢屬性及採取適當的措施。例如，為資料行加入標題屬性，並不會建立一個應用程式可顯示的標題。每個應用程式都必須設定為能夠讀取標題及正確顯示標題。

若要新增擴充屬性

若要更新擴充屬性

若要刪除擴充屬性

若要檢視擴充屬性