对数据库对象使用扩展属性

使用扩展属性,您可以添加文本(如描述性或指导性内容)、输入掩码和格式规则,将它们作为数据库中的对象或数据库自身的属性。例如,您可以将扩展属性添加到架构、架构的视图或视图中的列。由于扩展属性存储在数据库中,所有读取属性的应用程序都能以相同的方式评估对象。这有助于加强系统中所有程序对数据的处理方式的一致性。

扩展属性可以用于:

  • 指定表、视图或列的标题。这样,应用程序便可以在显示该表、该视图或该列的信息的用户界面中使用同一标题。

  • 为列指定输入掩码,以便应用程序可以在运行 Transact-SQL 语句之前验证数据。例如,可以在扩展属性中指定邮政编码或电话号码列的所需格式。

  • 指定数据在列中的显示格式规则。

  • 记录应用程序可显示给用户的特定数据库对象的描述。例如,这些描述可用在数据字典应用程序或报表中。

  • 指定某列显示的大小和窗口位置。

注意注意

不应将扩展属性用于隐藏关于对象的敏感信息。任何被授予对象权限的用户都可以查看该对象的扩展属性。例如,如果授予用户对表的 SELECT 权限,用户将能够查看表的扩展属性。

定义扩展属性

每个扩展属性都有用户定义的名称和值。扩展属性值是 sql_variant 值,最多可包含 7,500 个字节的数据。可以向一个对象添加多个扩展属性。

为了指定扩展属性,SQL Server 数据库中的对象分为三个级别:0、1 和 2。级别 0 是最高级别,定义为在数据库作用域内包含的对象。级别 1 的对象包含在架构作用域或用户作用域中,而级别 2 的对象包含在级别 1 的对象中。可以为这些级别中的任一级别的对象定义扩展属性。

引用某个级别中的对象必须用拥有或包含它们的更高级别对象的名称进行限定。例如,将扩展属性添加到表列(级别 2)时,还必须指定包含列的表名(级别 1)和包含表的架构(级别 0)。

在以下示例中,将扩展属性值 'Minimum inventory quantity.' 添加到 Production 架构所包含的 Product 表中的 SafetyStockLevel 列。

USE AdventureWorks2008R2;
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

 

 

 

示例

以下是将扩展属性添加到 AdventureWorks2008R2 示例数据库自身的示例。

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

默认

级别

级别类型

添加或删除扩展属性所需的权限

查看扩展属性所需的权限

注释

0

SCHEMA 或 USER

 

 

 

1

DEFAULT

对 SCHEMA 的 ALTER 权限

HAS_DBACCESS = 1

提供该列只是为了向后兼容。有关详细信息,请参阅SQL Server 2008 R2 中不推荐使用的数据库引擎功能

2

 

 

 

事件通知

级别

级别类型

添加或删除扩展属性所需的权限

查看扩展属性所需的权限

注释

0

EVENT NOTIFICATION

对 DATABASE 的 ALTER ANY DATABASE EVENT NOTIFICATION 权限

事件通知的所有者

对数据库的 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 AdventureWorks2008R2;
GO
EXEC sys.sp_addextendedproperty 
@name = N'MS_DescriptionExample', 
@value = N'Primary filegroup for the AdventureWorks2008R2 sample database.', 
@level0type = N'FILEGROUP', @level0name = [PRIMARY];
GO

以下是将扩展属性添加到 PRIMARY 文件组中的逻辑文件名称 AdventureWorks2008R2_Data 的示例。

USE AdventureWorks2008R2;
GO
EXEC sys.sp_addextendedproperty 
@name = N'MS_DescriptionExample', 
@value = N'Primary data file for the AdventureWorks2008R2 sample database.', 
@level0type = N'FILEGROUP', @level0name = [PRIMARY],
@level1type = N'Logical File Name', @level1name = AdventureWorks2008R2_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 AdventureWorks2008R2;
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 AdventureWorks2008R2;
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 AdventureWorks2008R2;
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 R2 中不推荐使用的数据库引擎功能

2

 

 

 

架构或用户

级别

级别类型

添加或删除扩展属性所需的权限

查看扩展属性所需的权限

注释

0

SCHEMA 或 USER

对 SCHEMA 的 ALTER 权限或对 USER 的 ALTER 权限

对 APPLICATION ROLE 的任意权限

对 ROLE 的任意权限

对 USER 的任意权限

如果未指定级别 1 类型,则属性将应用于架构或用户自身。

USER 可以是下列用户之一:

  • 应用程序角色

  • 数据库角色

  • SQL Server 用户

  • Windows 组

  • Windows 用户

只能在为用户添加扩展属性时才应使用 USER。

重要说明重要提示
在 SQL Server 的未来版本中,将删除对级别 1 或级别 2 类型对象定义的级别 0 类型的 USER。请避免在新的开发项目中使用此功能,并计划修改当前使用此功能的应用程序。改用 SCHEMA 作为级别 0 类型。请参阅本主题后面的“架构与用户”部分。

1

不定

 

 

 

2

不定

 

 

 

示例

在以下示例中,向 HumanResources 架构中添加一个扩展属性。

USE AdventureWorks2008R2;
GO
EXECUTE sys.sp_addextendedproperty 
@name = N'MS_Description',
@value = N'Contains objects related to employees and departments.',
@level0type = N'SCHEMA', 
@level0name = HumanResources;

在以下示例中,创建一个应用程序角色并且向该角色中添加一个扩展属性。

USE AdventureWorks2008R2;
GO
CREATE APPLICATION ROLE Buyers
WITH Password = '987G^bv876sPY)Y5m23';
GO
EXEC sys.sp_addextendedproperty 
@name = N'MS_Description', 
@value = N'Application Role for the Purchasing Department.',
@level0type = N'USER',
@level0name = N'Buyers';

服务

级别

级别类型

添加或删除扩展属性所需的权限

查看扩展属性所需的权限

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 AdventureWorks2008R2;
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 AdventureWorks2008R2;
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 R2 中不推荐使用的数据库引擎功能

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_listextendedpropertysys.extended_properties 以获得属性,并且必须将强制掩码的逻辑添加到所有的程序中。

复制扩展属性

扩展属性只在初始同步过程中在发布服务器和订阅服务器之间进行复制。如果在初始同步之后添加或修改扩展属性,则不会复制该更改。有关复制数据库对象的详细信息,请参阅发布数据和数据库对象

使用应用程序中的扩展属性

扩展属性仅提供在其中存储数据的指定位置。必须对所有应用程序进行编码以查询属性并采取适当的操作。例如,将标题属性添加到列并不能创建可由应用程序显示的标题。必须对每个应用程序进行编码才可正确地读取和显示标题。

添加扩展属性

更新扩展属性

删除扩展属性

查看扩展属性