迈向自文档化数据库：扩展属性

• 下载源文件 - 3 Kb

引言

在 SQL Server 中，多年来推出的一些最重大的改进却悄然问世，这真是令人奇怪。相反，那些被大肆宣传的功能有时却会成为死胡同。

属于前者一类的功能是数据库的 **扩展属性**。它随 SQL Server 2000 悄然推出，很容易被人忽略，但事后证明，它对开发人员来说价值非凡。扩展属性是创建自文档化数据库的途径。我使用“自文档化”这个术语，是指可以将文档直接附加到数据库本身，而不是创建单独的文档。基本上，您使用数据对象的扩展属性来应用您自己的属性到元数据。

使用扩展属性

经典的例子是使用对象的扩展属性来附加该对象的描述，无论是触发器、存储过程、函数、表、视图、参数、索引、约束还是列。还可以使用扩展属性来记录诸如修订日期、作者、输入掩码、标题、历史记录、渲染样式等细节。

过去，程序员用来为源代码添加文档的一个经典通用技巧是，通过插入预定义的标记来组织源代码注释，以指示信息类别（例如，修订日期），从而为任何生成文档的应用程序（如 Javadocs）提供信息。这在 SQL Server 中是无法做到的，因为源代码仅在数据库中保留用于某些对象，例如过程和函数。无论如何，自扩展属性引入以来，这种技巧就变得不必要了。

使用扩展属性的优点是，文档、注释等信息会与数据库一起保留，并可用作“实时文档”机制。它们会随数据库一起备份，并与构建脚本一起脚本化。

尽管具有明显的实用性，但微软对该功能却表现出一种奇怪的冷漠。其设计有些“未完工”的感觉。扩展属性允许您记录数据库对象，但却将利用这些属性生成数据库自身完整文档的任务留给了第三方实用工具，例如 SQL Doc 和 DBDesc。

微软对扩展属性漠不关心的后果是，他们忘记将其包含在复制同步过程中。您必须手动处理（例如，使用 SQL Compare 等工具可以正确同步它们）。此外，他们还忽略了提供扩展属性的 **Information_Schema** 视图，这本可以使从 SQL 访问它们更加容易。

另一个困难在于，一些第三方软件供应商已将扩展属性用于其他目的，例如存储实体关系图的参数。这使得提取文档的实用工具难以处理，因为除了 MS_Description 之外，没有标准的属性名称。

通过代码创建扩展属性

微软提供了一个扩展属性 MS_Description，可以从 Enterprise Manager 和 SSMS 使用它来提供绑定对象的描述。此外，Diagram Designer 提供了一个描述字段，可以通过自定义视图访问，从而提供了一种方便地查看和编辑列文档的方法。

然而，扩展属性主要用于提供对象的基本描述。它们的功能远不止于此。扩展属性的设计者明智地没有对可以应用于数据库对象的属性设置任何限制。例如，提供额外的元数据来帮助应用程序层渲染或查询数据是完全可以的。

在编写对象文档时，通常最快捷的方式是利用微软自己的工具来添加基本描述，但除此之外，最终总会需要使用存储过程来添加文档。

在基本层面，在 SQL Server 2000 中，所有扩展属性都存储在 sysproperties 中，但通过一系列存储过程进行访问。

<><><><TD><CODE>sp_addextendedproperty </> <td>为数据库对象添加一个 新的 扩展属性</td> </></>

sp_dropextendedproperty	从数据库对象中移除一个扩展属性
sp_updateextendedproperty	更新现有扩展属性的值
fn_listextendedproperty	检索数据库对象上某个扩展属性的值或所有扩展属性的列表

这些存储过程使用起来很麻烦，几乎不会鼓励程序员使用扩展属性。然而，几个（针对 SQL Server 2000 的）示例可能会使它们的用法更清晰一些。

/*we add the extended property to provide a description to the 
  dbo.Customer.InsertionDate column   */
sp_addExtendedProperty 'MS_Description', 
               'the date at which the row was created',  
               'user', 'dbo', 'table', 'Customer', 'column', 'InsertionDate' 

-- alternative syntax for SQL 2005
sp_addExtendedProperty 'MS_Description', 
               'the date at which the row was created', 
               'schema','sales', 'table', 'Customer', 'column', 'ModifiedDate'

/* and then update the description of the dbo.Customer.InsertionDate column */ 
sp_UpdateExtendedProperty 'MS_Description', 
         'the full date at which the row was created',  
         'user', 'dbo', 'table', 'Customer', 'column', 'InsertionDate' 

/* we can list this column */ 
SELECT * FROM ::fn_listExtendedProperty  
  ( 'MS_Description','user', 'dbo', 'table', 'Customer', 'column', 
    'InsertionDate') 

/* or all the properties for the table column of dbo.Customer*/ 
SELECT * FROM ::fn_listExtendedProperty     
   (DEFAULT,'user', 'dbo', 'table', 'Customer', 'column', DEFAULT) 

/* And now we drop the MS_Description property of dbo.Customer.InsertionDate 
   column     */ 
sp_dropExtendedProperty 'MS_Description',   
'user', 'dbo', 'table', 'Customer', 'column', 'InsertionDate

一个数据库文档化存储过程

我觉得前面部分描述的存储过程不够直观，并不怎么能吸引程序员去添加文档。我更希望有一个以更传统的方式描述对象及其层次结构的东西。例如，如果想修改 Customer 表中的 surname 列的描述，应该使用 'user.table.column' 层次结构，即 'dbo.Customer.Surname.MS_Description'。

为了简化操作，我创建了一个简单的“助手”存储过程，它简化了对微软系统存储过程的访问，但并不试图取代它们。如果您提供对象的描述和层次结构，它就会显示存在的内容。如果您提供一个值，它会对其进行赋值，或者，如果您愿意，可以将其附加到当前值的末尾。

该存储过程会自动检测它所处的 SQL Server 版本，并加载适用于操作系统的有效对象层次结构。它会检查您提供的层次结构是否有效。这个列表非常有用，所以存储过程还包含一个提供层次结构作为表的功能。

该 spDBDoc 存储过程的完整源代码在本文章的源代码中提供（只需点击文章标题右侧框中的“代码下载”链接）。

创建存储过程并尝试一下。首先，让我们看看哪些层次结构可以拥有扩展属性。

EXEC spDbDoc '','','','possible'

或者，也可以这样

spDbDoc @Function='possible'

您会发现有很多——并且 SQL 2005 中可以附加属性的可能对象大大增加了（粗体标出的在 2000 和 2005 中都可用）。

assembly	Schema.Service	user.function.Constraint
contract	Schema.Synonym	user.function.Parameter
Event Notification	Schema.Table	user.Procedure
fileGroup.Logical file Name	Schema.Table.Column	user.Procedure.Parameter
Message type	Schema.Table.Constraint	user.Queue
partition Function	Schema.Table.Index	user.Queue.Event Notification
partition Scheme	Schema.Table.Trigger	User.Rule
Remote Service Binding	Schema.Type	user.Service
route	Schema.View	user.Synonym
架构	Schema.View.Trigger	User.Table
schema.aggregate	Schema.View.column	User.Table.Column
schema.Default	Schema.View.index	User.Table.Constraint
schema.function	Schema.XML Schema Collection	User.Table.Index
schema.function.column	Service	User.Table.Trigger
schema.function.Constraint	触发器	user.Type
schema.function.Parameter	type	User.View
schema.Procedure	用户	User.View.column
schema.Procedure.Parameter	user.aggregate	User.View.index
schema.Queue	user.Default	User.View.Trigger
schema.Queue.Event Notification	user.function	User.XML Schema Collection
schema.Rule	user.function.column

让我们从最简单的开始。我们只为整个数据库创建一个描述。

EXEC spdbDoc '','',
    'This is a sample database that illustrates how extended properties 
can be assigned to objects' 

这里没什么激动人心的，所以我们添加一个“修订日期”属性。

EXEC spdbDoc '','revisionDate','20 Nov 2006: Built the first iteration' 

现在我们要添加新版本，而不是替换现有值。

EXEC spdbDoc '','revisionDate',
             '21 Nov 2006: Fixed warning message in build script','append' 

例如，我们可以列出给定表（比如“customer”表）的列的所有扩展属性和值。

EXEC spdbDoc 'user.table.column','dbo.customer' -- SQL 2000 
EXEC spdbDoc 'schema.table.column','sales.customer' -- SQL 2005 

分配一个标准的 MS_Documentation 属性和值很容易。

--either alter or create an entry for the MS_Documentation property for 
--insertionDate 
spdbDoc 'user.table.column','dbo.customer.insertionDate',
        'This logs the date that the row was inserted' 

或者您可以添加自己的属性。例如，我们为日期字段分配了一个建议的转换样式。

/* either alter or create an entry for the ConvertStyle property for 
   insertionDate */
EXEC spdbDoc 'user.table.column','dbo.customer.insertionDate.ConvertStyle',
             113 

如果您查看源代码下载，会发现其中包含更多作为注释的该过程的示例。

当然，归根结底，扩展属性只是确保数据库文档齐全且易于理解的各种方法之一，例如使用长描述性的对象名称。我们欢迎您的贡献——请在下方留言，或发送电子邮件至编辑 (editor@simple-talk.com)。对于帮助数据库本身实现自文档化的最佳五种建议将获得奖品！