如何记录SQL Server数据库对象

介绍 (Introduction)

In any good programming reference, you will read that a developer has to document his code, not only for him/herself but also for the person who, ten years later will be asked to maintain it. This would, of course, be made easier thanks to a good documentation of existing code.

在任何良好的编程参考中，您都会读到，开发人员不仅必须为自己编写代码，而且还要为十年后被要求维护代码的人编写代码。 当然，这要归功于现有代码的良好文档。

But, documentation is not simply some document we can open when we need it. It’s a complete process that should start no later than the end of every development task with other processes, like Code Quality Assessment and Testing. In the best case, both of these processes should take documentation into account. For instance, a unit test that fails whenever an undocumented object is found could be created. If everything is done well, then we are sure that each version of a database solution that is distributed always contains documentation. Note that a unneglectable step of this documentation process should be a regular review of existing documentation so that it stays up-to-date and reflects reality.

但是，文档不仅仅是我们可以在需要时打开的一些文档。 这是一个完整的过程，应该不迟于每个开发任务与其他过程（如代码质量评估和测试）一起开始。 在最好的情况下，这两个过程都应考虑文档。 例如，可以创建一个只要发现未记录对象就失败的单元测试。 如果一切顺利，那么我们可以确保分发的数据库解决方案的每个版本始终包含文档。 请注意，此文档编制过程中不可忽视的步骤应该是对现有文档进行定期审查，以使其保持最新状态并反映实际情况。

The following figure depicts and summarizes development workflow as discussed above.

下图描绘并总结了如上所述的开发工作流程。

As SQL Server database development is a good candidate for code documentation, this development workflow applies and this article will focus on following steps:

由于SQL Server数据库开发是代码文档的理想选择，因此此开发工作流程适用，本文将重点介绍以下步骤：

• Inventory assets to document
  要记录的库存资产
• Describe undocumented assets
  描述无证资产
• Review documented assets
  查看记录的资产

In the following sections, we will first review a solution provided by Microsoft for documenting SQL Server database objects and come to the conclusion that this has some missing features and that we could define another way to document our code.

在以下各节中，我们将首先回顾Microsoft提供的用于记录SQL Server数据库对象的解决方案，并得出结论，该解决方案具有一些缺少的功能，并且可以定义另一种方法来记录我们的代码。

Then we will discuss a first solution that consists in the implementation of a stored procedure that calls the built-in CRUD components presented above in order to manage extended properties from a single-entry point.

然后，我们将讨论第一个解决方案，该解决方案包括存储过程的实现，该存储过程调用上面介绍的内置CRUD组件，以便从单入口点管理扩展属性。

However, this solution won’t overcome some aspects. So, we will review a second solution that will implement steps in development workflow related to documentation.

但是，此解决方案不会克服某些方面。 因此，我们将回顾第二种解决方案，该解决方案将实现与文档相关的开发工作流中的步骤。

• The first step, Inventory assets to document, will leverage a corresponding stored procedure that will populate one or more SQL tables based on objects in the INFORMATION_SCHEMA schema and optionally consider existing extended properties.
• 第一步，要记录的库存资产，将利用相应的存储过程，该过程将基于INFORMATION_SCHEMA模式中的对象填充一个或多个SQL表，并可以选择考虑现有的扩展属性。
• The second and third steps are more of a manual process and we will discuss a set of database objects that will become helpful in the completion of this process and the way to chain them.
  第二步和第三步更多是一个手动过程，我们将讨论一组数据库对象，这些对象将有助于完成此过程以及链接它们的方式。

Finally, as we should not close the door to direct extended property usage, we will review a stored procedure that will take the data back from the documentation we did, using the review process, and apply the documentation to extended properties for each SQL database.

最后，由于我们不应该直接指导扩展属性的使用，我们将审阅存储过程，该存储过程将使用审阅过程从我们所做的文档中获取数据，并将文档应用于每个SQL数据库的扩展属性。

Important Note

重要的提示

Some of the presented procedures are still under development and have only been tested with common SQL Server database objects like tables, views, procedures, types, and functions. Handling of other kinds of objects like those related to SQL Server Service Broker is still to be implemented.

所提供的某些过程仍在开发中，仅使用常见SQL Server数据库对象（如表，视图，过程，类型和函数）进行了测试。 其他种类的对象（例如与SQL Server Service Broker相关的对象）的处理仍将实现。

MicrosoftSQL Server文档解决方案：扩展属性 (Microsoft’s solution for SQL Server documentation: Extended Properties)

Well, SQL Server developers at Microsoft may agree with the principle of documenting database development. This is certainly the reason why we can find a feature called Extended Properties since (at least) SQL Server 2005 version.

嗯，MicrosoftSQL Server开发人员可能同意记录数据库开发的原理。 自从（至少）从SQL Server 2005版本开始，我们当然可以找到一个名为“扩展属性”的功能。

You can imagine it as metadata associated with a given database object (and even a database itself). Since the metadata are stored in the database, it are accessible to any database principal with sufficient privileges. Furthermore, it is backed up and restored whenever the database is respectively backed up or restored.

您可以将其想象为与给定数据库对象（甚至是数据库本身）关联的元数据。 由于元数据存储在数据库中，因此具有足够特权的任何数据库主体都可以访问它。 此外，无论何时分别备份或还原数据库，都将对其进行备份和还原。

We can define multiple extended properties to a single object. These properties can bear the name you want but there are commonly used names like:

我们可以为单个对象定义多个扩展属性。 这些属性可以带有您想要的名称，但是有一些常用的名称，例如：

• ‘MS_Description’ that is used by multiple tools as the name for a description property of the object. We will talk about it again later in this article.
• 被多个工具用作对象的描述属性的名称的“ MS_Description” 。 我们将在本文后面再次讨论。
• ‘Version’ and ‘VersionDate’ (mainly for database or schema objects)
• “版本”和“版本 日期” （主要用于数据库或架构对象）

These extended properties can be accessed using a T-SQL query to sys.extended_properties table.

可以使用T-SQL查询sys.extended_properties表来访问这些扩展属性。

Here is a sample result of a query that selects every column from that table.

这是从该表中选择每一列的查询的示例结果。

Records in that table can be respectively created, edited or removed using following stored procedures. Their names speak for themselves.

可以使用以下存储过程分别创建，编辑或删除该表中的记录。 他们的名字说明了一切。

• sys.sp_addextendedproperty
• sys.sp_addextendedproperty
• sys.sp_updateextendedproperty
• sys.sp_updateextendedproperty
• sys.sp_dropextendedproperty
• sys.sp_dropextendedproperty

There is also a built-in function that allows you to list extended properties based on a series of criteria. This procedure is called sys.fn_listextendedproperty. Those four programmable components will be referred as built-in CRUD components in the remainder of the article.

还有一个内置函数，可让您根据一系列条件列出扩展属性。 此过程称为sys.fn_listextendedproperty 。 在本文的其余部分中，这四个可编程组件将称为内置CRUD组件。

We could also access these properties using SQL Server Management Studio by right-clicking on a database object and choosing to display its properties. In the dialog, we will find an Extended Properties tab.

我们还可以使用SQL Server Management Studio通过右键单击数据库对象并选择显示其属性来访问这些属性。 在对话框中，我们将找到“扩展属性”选项卡。

While this feature seems very helpful, it’s really underused and there are simple reasons for it:

尽管此功能似乎很有帮助，但它的使用确实很少，原因很简单：

• Lack of knowledge: developers do not even know it exists
  缺乏知识：开发人员甚至都不知道它的存在
• Lack of time
  时间不够
• Usage of three stored procedures above is not that simple: the add procedure fails when a record exists, the update one when there is no defined extended property
  上面三个存储过程的用法不是那么简单：存在记录时添加过程失败，没有定义扩展属性时更新过程失败
• One database at a time: as extended properties are stored in a database, you can’t query for extended properties from multiple databases in a single SELECT query.
• 一次一个数据库：由于扩展属性存储在数据库中，因此无法在单个SELECT查询中从多个数据库中查询扩展属性。
• Properties are dropped when the object is dropped
  放置对象时放置属性
• etc
  等等

解决方案1：用于管理扩展属性的存储过程 (Solution 1: a stored procedure to manage extended properties)

There are some problems in the list of potential reasons for the lack of adoption of extended properties that can be overcome with the implementation of a stored procedure. For instance, it would be easy to remove the obligation to use multiple objects and check whether an extended property already exists or not.

缺少采用扩展属性的潜在原因列表中存在一些问题，可以通过实现存储过程来克服这些问题。 例如，很容易消除使用多个对象的义务并检查扩展属性是否已经存在。

That the raison d’être of Utils.ManageExtendedProperty stored procedure. This stored procedure will take action of any extended property no matter the database. It will use have almost the same signature as built-in CRUD components for extended events management.

Utils.ManageExtendedProperty存储过程的存在理由。 无论数据库如何，此存储过程都将对任何扩展属性采取措施。 它将使用与内置CRUD组件几乎相同的签名进行扩展的事件管理。

Let’s review its parameters.

让我们回顾一下它的参数。

 
ALTER PROCEDURE [Utils].[ManageExtendedProperty] (
    @DatabaseName                   VARCHAR(256)   = NULL,
    @PropertyName                   VARCHAR(256)   = 'MS_Description',
    @PropertyValue                  VARCHAR(1024),
    @LevelType_0                    VARCHAR(128)   = 'SCHEMA',          
    @LevelName_0                    VARCHAR(128),
    @LevelType_1                    VARCHAR(128)   = NULL,    
    @LevelName_1                    VARCHAR(128)   = NULL,
    @LevelType_2                    VARCHAR(128)   = NULL,    
    @LevelName_2                    VARCHAR(128)   = NULL,
    @OperationMode                  VARCHAR(32)    = 'Overwrite',    
    @Debug                          BIT = 0
)
 

First, there is the database name in which our stored procedure should apply modifications. If no one is provided, it will take current database name.

首先，存在我们的存储过程应在其中应用修改的数据库名称。 如果没有提供，它将使用当前的数据库名称。

Then, there is the name of the extended property on which we want to operate followed by common level<X>(type|name) parameters of sp_<action>extendedproperty built-in CRUD components.

然后，是我们要在其上操作的扩展属性的名称，后跟sp_ <action> extendedproperty内置CRUD组件的公共level <X>（type | name）参数。

Finally, we can find @OperationMode and @Debug parameters. First one will allow its user to tell stored procedure which action it has to take for this set of parameters. There are 4 operation modes:

最后，我们可以发现@OperationMode和@Debug参数。 第一个将允许其用户告诉存储过程必须对这组参数采取哪些操作。 有4种操作模式：

1. OVERWRITE (It’s the default. It creates or replaces value for the extended property)
2. OVERWRITE （默认设置。它创建或替换扩展属性的值）
3. APPEND (reads existing property if any and append provided value for @PropertyValue parameter to it.)
4. APPEND （读取现有属性（如果有），并将@PropertyValue参数的提供值附加到该属性。）
5. PREPEND (reads existing property if any and prepend provided value for @PropertyValue parameter to it.)
6. PREPEND （读取现有属性（如果有），并在其之前为@PropertyValue参数提供的值。）
7. REMOVE (calls sp_dropextendedproperty)
8. 删除 （调用sp_dropextendedproperty ）

First three modes will generate a call to sp_addextendedproperty stored procedure whenever parameter set leads to an undefined extended property. In contrast, when an extended property is defined, then it will call sp_updateextendedproperty stored procedure.

每当参数集导致未定义的扩展属性时，前三种模式将生成对sp_addextendedproperty存储过程的调用。 相反，定义扩展属性后，它将调用sp_updateextendedproperty存储过程。

Note

注意

1. The creation script for this stored procedure is attached to this article.
   本文附带了此存储过程的创建脚本。
2. This stored procedure is not complete and some additional work should be done on it so that it covers all the cases, but it works in all the cases presented here.
   此存储过程尚不完整，应该对其进行一些其他工作，以使其涵盖所有情况，但它适用于此处介绍的所有情况。

While this stored procedure could simply make the call, it does extra checks like:

尽管此存储过程可以简单地进行调用，但它会进行额外的检查，例如：

• Checking target schema exists
  检查目标架构是否存在
• Checking target object exists
  检查目标对象是否存在
• Checking target attribute exists
  检查目标属性是否存在
• …
  …

If one of those checks fails, then an error is raised and no modification is done to values of existing extended properties.

如果这些检查之一失败，则会引发错误，并且不对现有扩展属性的值进行任何修改。

Note

注意

There is also a parameter @_NoChecks which, when set to 1, tells stored procedure not to perform those checks. In that case, all accepted parameter associations that are defined in the stored procedure body will affect corresponding extended properties. No extensive tests have been made with this mode but it should work

还有一个参数@_NoChecks ，当它设置为1时，它告诉存储过程不要执行那些检查。 在这种情况下，存储过程主体中定义的所有接受的参数关联都将影响相应的扩展属性。 没有使用此模式进行过广泛的测试，但它应该可以工作

In contrast, when @_NoChecks default value (0) is used, it will take into account only extended properties corresponding to accepted parameters associations, for which additional checks are defined and which corresponding values passed those checks.

相反，当使用@_NoChecks默认值（0）时，它将仅考虑与接受的参数关联相对应的扩展属性，为此定义了附加检查，以及哪些对应值通过了这些检查。

The algorithm of this stored procedure is quite simple:

此存储过程的算法非常简单：

1. Check parameters associations validity
   检查参数关联的有效性
2. (Optional) check underlying objects exists
   （可选）检查基础对象是否存在
3. Get extended properties if any
   获取扩展属性（如果有）
4. (Optional) Append or prepend the extended property value
   （可选）附加或添加扩展属性值
5. • Delete extended property if @OperationMode is set to ‘REMOVE’ and an extended property exists
   • 如果@OperationMode设置为'REMOVE'并且存在扩展属性，则删除扩展属性
   • Create an extended property if no one exists
     如果没有人创建扩展属性
   • Update the extended property if it already exists
     更新扩展属性（如果已存在）

Here is an example call to this procedure in the context of documenting a function called Testing.GetDocTypeCount in TestSchemaDoc database.

这是在记录TestSchemaDoc数据库中名为Testing.GetDocTypeCount的函数的上下文中对此过程的示例调用。

 
EXEC [Utils].[ManageExtendedProperty]
               @DatabaseName   = 'TestSchemaDoc',
               @LevelType_0    = 'SCHEMA',
               @LevelName_0    = 'Testing',
               @LevelType_1    = 'FUNCTION',
               @LevelName_1    = 'GetDocTypeCount',
               @LevelType_2    = 'PARAMETER',
               @LevelName_2    = '@DocType',
               @PropertyValue  = 'Kind of document',
               @Debug          = 1
;
 

解决方案2：自制文档解决方案 (Solution 2: homemade documentation solution)

As solution 1 does not overcome all the limitations of Extended Properties, we will design our own solution.

由于解决方案1不能克服扩展属性的所有限制，因此我们将设计自己的解决方案。

In following, we will first review different objects that are core components of the implementation of the homemade documentation solution. Basically, they are tables or views. Then, we will review the 3 steps in development workflow that are considered in this article.

接下来，我们将首先回顾不同对象，这些对象是自制文档解决方案实施的核心组成部分。 基本上，它们是表或视图。 然后，我们将回顾本文考虑的开发工作流程中的3个步骤。

Core objects overview

核心对象概述

Except when stated differently, all objects are in a schema called [Docs]. There are two tables:

除非另有说明，否则所有对象都处于称为[Docs]的架构中。 有两个表：

• SchemaObjectDictionary which is responsible to keep track of objects defined in a database. All objects that come with SQL Server won’t be part of records for this table.
• SchemaObjectDictionary ，它负责跟踪数据库中定义的对象。 SQL Server附带的所有对象都不属于该表的记录。
• SchemaObjectsAttributes which will keep records of columns defined for either tables or views so as routines (procedure/functions) related to a record of former table.
• SchemaObjectsAttributes ，将保留为表或视图定义的列的记录，以便与先前表的记录相关的例程（过程/函数）。

Here is the relationship between those tables:

这是这些表之间的关系：

Note

注意

• As this documentation could be externalized, we already added a ServerId column which will be NULL on a single SQL Server Instance. This column could be removed from all code here if you don’t want it.
• 由于本文档可以外部化，因此我们已经添加了ServerId列，该列在单个SQL Server实例上为NULL 。 如果您不想要此列，可以从此处的所有代码中删除。
• You could notice the hasBeenDeleted column. With this, documentation can persist an object or attribute deletion. Documentation manager should define the way these records should be managed.
• 您可能会注意到hasBeenDeleted列。 这样，文档可以保留对象或属性的删除。 文档管理器应定义这些记录的管理方式。

We will create CRUD stored procedure for each table. This stored procedure will be attached to this article and will be called using the following naming convention:

我们将为每个表创建CRUD存储过程。 此存储过程将附加到本文，并将使用以下命名约定进行调用：

 
<ProcedureName>_<Action>
 

Where <Action> is either:

其中<Action>为：

• Insert
  插
• Update
  更新资料
• Delete
  删除
• Upsert (insert or update)
  向上插入（插入或更新）

As SchemaObjectsAttributes table only contains a reference to a schema object definition in SchemaObjectDictionary table, we will create a view that will join both tables. This view is called SchemaObjectsAttributesDetails and the query use to define it is as follows:

由于SchemaObjectsAttributes表仅包含对SchemaObjectDictionary表中的模式对象定义的引用 ，因此我们将创建一个将两个表连接在一起的视图。 该视图称为SchemaObjectsAttributesDetails ，用于定义它的查询如下：

 
select 
        od.DatabaseName, 
        od.SchemaName,
        od.ObjectName,
        cd.AttributeName,
        od.ObjectType,
        od.isDepreciated as ObjectIsDepreciated,
        od.ObjectDescription,
        cd.AttributeDescription,
        od.DefinitionId as ObjectDefinitionId,
        cd.DefinitionId as AttributeDefinitionId
    From Docs.SchemaObjectDictionary od 
    inner join Docs.SchemaObjectsAttributes cd
    on od.DefinitionId = cd.ObjectDictionaryId
;
 

Here is a sample output for this query:

这是此查询的示例输出：

As final objective is to document every SQL Server object inside a given database, we will also create a view that we will call SchemaObjectDictionaryMissingDescription. This view will return a list of objects with missing documentation either about itself or about one or more of its columns.

由于最终目标是记录给定数据库内的每个SQL Server对象，因此我们还将创建一个称为SchemaObjectDictionaryMissingDescription的视图。 该视图将返回一个对象列表，该对象列表缺少有关其自身或其某个或多个列的文档。

If possible, we could generate a T-SQL statement. We would edit this statement in order to update record and make it disappear from records returned by that view. This goal will be achieved by DDL2UpdateDescription column. We could be willing to get details about related objects (or columns). This operation will be available using DDL2GetDependentDescription column.

如果可能，我们可以生成T-SQL语句。 我们将编辑此语句以更新记录，并使该记录从该视图返回的记录中消失。 此目标将通过DDL2UpdateDescription列实现。 我们可能愿意获得有关相关对象（或列）的详细信息。 使用DDL2GetDependentDescription列可以使用此操作。

In terms of implementation, DDL2UpdateDescription column will contain a call to SchemaObjectDictionary_Upsert procedure that runs an INSERT or UPDATE statement like a MERGE would do while DDL2GetDependentDescription column will call a stored procedure called GetRelatedObjectDescription that will query SchemaObjectsAttributes table to get back attributes and their description of a particular object. The creation script for this stored procedure is also attached to this article.

在实现方面， DDL2UpdateDescription列将包含对SchemaObjectDictionary_Upsert过程的调用， 该过程将像MERGE一样运行INSERT或UPDATE语句，而DDL2GetDependentDescription列将调用名为GetRelatedObjectDescription的存储过程，该存储过程将查询SchemaObjectsAttributes表以获取属性及其描述。特定的对象。 此存储过程的创建脚本也随附于本文。

Implementation of “Inventory assets to document” step

实施“库存资产记录”步骤

Considerations

注意事项

Now that the core tables are defined, we still need to populate them. We could do it manually and get used to creating a record each time we create a new table but how would we do for already existing systems?

现在已经定义了核心表，我们仍然需要填充它们。 我们可以手动执行此操作，并在每次创建新表时习惯于创建记录，但是如何处理现有系统呢？

We will need to create a stored procedure that will do the job for us. Let’s call it PopulateSchemaObjectDictionary. In order to test it and also to get an overview of extended properties, we will define a parameter called @ReadonlyExecution so that, when set to 1, will tell stored procedure not to update dictionary but to display lookup results instead.

我们将需要创建一个存储过程来为我们完成这项工作。 我们称之为PopulateSchemaObjectDictionary 。 为了对其进行测试并获得扩展属性的概述，我们将定义一个名为@ReadonlyExecution的参数，以便将其设置为1时，将告诉存储过程不更新字典，而是显示查找结果。

Moreover, we might know that a lot of tools with extended features, like ApexSQL Doc, that help a developer to document its SQL Server database objects use an extended property called MS_Description.

此外，我们可能知道很多具有扩展功能的工具（例如ApexSQL Doc ）都可以使用称为MS_Description的扩展属性来帮助开发人员记录其SQL Server数据库对象。

As we will consider all objects inside the database, we should also consider getting back existing values from these extended properties. By the way, we should let the door open to the adoption of another standard and let user the capability to change the name of the extended property to consider.

当我们考虑数据库中的所有对象时，我们还应该考虑从这些扩展属性中获取现有值。 顺便说一下，我们应该为采用其他标准敞开大门，让用户能够更改要考虑的扩展属性的名称。

Finally, as an application is not always limited to a single database, our stored procedure will run by default against all user databases except if a value for a DatabaseName parameter is provided. We also added a filter @SchemaName to take only objects from that schema name.

最后，由于应用程序并不总是局限于单个数据库，因此默认情况下，我们的存储过程将针对所有用户数据库运行，除非提供了DatabaseName参数的值。 我们还添加了一个过滤器@SchemaName以仅采用该架构名称中的对象。

This will give us following signature for PopulateSchemaObjectDictionary stored procedure:

这将为我们提供以下PopulateSchemaObjectDictionary存储过程的签名：

 
ALTER PROCEDURE [Docs].[PopulateSchemaObjectDictionary] (
    @ServerId                           BIGINT          = NULL,
    @DatabaseName                       VARCHAR(256)    = NULL,
    @SchemaName                         VARCHAR(256)    = NULL,
    @ReadFromExtendedProperties         BIT             = 1,
    @ExtendedPropertyName               VARCHAR(256)    = 'MS_Description',
    @PreserveDescriptionInDictionary    BIT             = 1,
    @ReadonlyExecution                  BIT             = 0,
    @Debug                              BIT             = 0
)
 

Implementation details

实施细节

The code for this procedure is provided with this article. We will review some details in its implementation.

本文提供了此过程的代码。 我们将审查其实施中的一些细节。

It’s a three-step process that is summarized with following diagram:

这是一个三步过程，总结如下图：

Initializations and temporary tables creation

初始化和临时表创建

This procedure will first create two temporary tables #DiscoveredSchemaObjects and #DiscoveredDataObjectsProperties that are similar to both tables discussed above.

此过程将首先创建两个临时表#DiscoveredSchemaObjects和#DiscoveredDataObjectsProperty ，它们与上面讨论的两个表相似。

Temporary tables population

临时表人口

Then it will populate #DiscoveredSchemaObjects temporary table using information from following tables or views in each database:

然后，它将使用每个数据库中以下表或视图中的信息填充#DiscoveredSchemaObjects临时表：

INFORMATION_SCHEMA.TABLES	to get a list with all user defined tables
INFORMATION_SCHEMA.VIEWS	to get a list with all user defined views
INFORMATION_SCHEMA.ROUTINES	to get a list of all user defined stored procedure, function…
INFORMATION_SCHEMA.DOMAINS	to get a list of user-defined data types

INFORMATION_SCHEMA.TABLES	获取包含所有用户定义表的列表
INFORMATION_SCHEMA.VIEWS	获取具有所有用户定义视图的列表
INFORMATION_SCHEMA.ROUTINES	获取所有用户定义的存储过程，函数的列表...
INFORMATION_SCHEMA.DOMAINS	获取用户定义的数据类型列表

This will constitute a good starting point but should be extended in the future with sequences and other kinds of database objects.

这将是一个很好的起点，但将来应与序列和其他类型的数据库对象一起扩展。

Note

注意

The loop that runs the query to get back information has been replaced by Common.RunQueryAcrossDatabases stored procedure that will also be attached to this article but can to be replaced by either sp_MSForEachDB or another piece of T-SQL code.

运行查询以获取信息的循环已替换为Common.RunQueryAcrossDatabases存储过程，该存储过程也将随附于本文，但可以由sp_MSForEachDB或另一段T-SQL代码替换。

The population of #DiscoveredDataObjectsProperties temporary table will be performed by following query. This query will be run against all databases of interest.

#DiscoveredDataObjectsProperties临时表的填充将通过以下查询执行。 该查询将针对所有感兴趣的数据库运行。

 
INSERT INTO #DiscoveredDataObjectsProperties(
    DatabaseName,DbObjectId,ColumnName,OrdinalPosition
) 
select 
    DB_NAME() as DatabaseName,
    objs.DbObjectId,
    cols.COLUMN_NAME as ColumnName,
    cols.ORDINAL_POSITION as OrdinalPosition
From INFORMATION_SCHEMA.COLUMNS cols
INNER JOIN #DiscoveredSchemaObjects objs
ON cols.TABLE_CATALOG = objs.DatabaseName
AND cols.TABLE_SCHEMA = objs.SchemaName
AND cols.TABLE_NAME   = objs.ObjectName 
UNION ALL
SELECT
    DB_NAME() as DatabaseName,
    objs.DbObjectId,
    parms.PARAMETER_NAME as ColumnName,
    parms.ORDINAL_POSITION as OrdinalPosition
FROM INFORMATION_SCHEMA.PARAMETERS parms
INNER JOIN #DiscoveredSchemaObjects objs
ON parms.SPECIFIC_CATALOG = objs.DatabaseName
AND parms.SPECIFIC_SCHEMA = objs.SchemaName
AND parms.SPECIFIC_NAME   = objs.ObjectName 
WHERE parms.PARAMETER_NAME IS NOT NULL
;
 

Then the stored procedure will take @ReadFromExtendedProperties and @ExtendedPropertyName parameter values and eventually query sys.extended_properties table in each database of interest in order to update description for collected objects and columns/parameters.

然后，存储过程将使用@ReadFromExtendedProperties和@ExtendedPropertyName参数值，并最终在每个感兴趣的数据库中查询sys.extended_properties表，以更新对收集的对象和列/参数的描述。

Finally, based on the value of @ReadonlyExecution parameter, we will either return contents of both temporary tables (#DiscoveredSchemaObjects and #DiscoveredDataObjectsProperties) or update dictionary tables (SchemaObjectDictionary and SchemaObjectsAttributes).

最后，基于@ReadonlyExecution参数的值，我们将返回两个临时表（ #DiscoveredSchemaObjects和#DiscoveredDataObjectsProperties ）的内容，或者更新字典表（ SchemaObjectDictionary和SchemaObjectsAttributes ）。

Actual dictionary update

实际字典更新

Actual dictionary update is performed using two MERGE statements. Data source for these statements are temporary tables discussed above.

使用两个MERGE语句执行实际的词典更新。 这些语句的数据源是上面讨论的临时表。

Calling the stored procedure

调用存储过程

Following code will call population stored procedure for current database.

以下代码将调用当前数据库的填充存储过程。

 
DECLARE @DbName SYSNAME;
SET @DbName = DB_NAME();
EXEC [Docs].[PopulateSchemaObjectDictionary] 
                    @DatabaseName = @DbName, 
                    @Debug = 1
 

As we are in Debug mode, we can scroll down in the Messages part of SSMS and we will get a report on the number of objects it found.

当我们处于“调试”模式时，我们可以在SSMS的“消息”部分中向下滚动，并获得有关找到的对象数量的报告。

Here is a sample of first collection.

这是第一批样品。

Note

注意

Only 17 objects are displayed and we got 26 objects reported to have been inserted. This is due to the fact that I activated database diagrams and I got a set of objects from dbo schema related to this feature of SSMS. These tables are not shipped with SQL Server.

仅显示17个对象，并且报告有26个对象已插入。 这是由于我激活了数据库图，并且从dbo模式中获得了一组与SSMS的此功能相关的对象。 这些表不随SQL Server一起提供。

Documentation review (the other steps)

文档审查（其他步骤）

Well, now let’s say we have populated core tables and we are at step 2 which, as a reminder, is entitled “Describe undocumented assets”.

好了，现在让我们说我们已经填充了核心表，并且我们处于第2步，作为提醒，我们的标题为“描述无证资产”。

To do so, we will call Docs.SchemaObjectDictionaryMissingDescription. This view will provide us a list with objects that has either not description for themselves or for one or more of their attributes.

为此，我们将调用Docs.SchemaObjectDictionaryMissingDescription 。 该视图将为我们提供一个对象列表，这些对象本身或它们的一个或多个属性没有描述。

Here is a sample results of a query to this view to get back objects from dbo schema.

这是对该视图进行查询以从dbo模式中获取对象的示例结果。

There are two columns we need to look at. First one is the MissingDescription column. If its value is 1, then we need to document the SQL Server object itself. This can be performed easily by editing and running the statement we get back from DDL2UpdateDescription column.

我们需要查看两列。 第一个是MissingDescription列。 如果其值为1，则我们需要记录SQL Server对象本身。 通过编辑和运行我们从DDL2UpdateDescription列返回的语句，可以轻松地执行此操作。

Here is the value taken from second row in previous example.

这是上一示例中第二行的值。

The second column to look at is DependentMissingDescriptionCount. If its value is bigger than 0, then we should copy and execute the statement from DDL2GetDependentDescription column value.

要查看的第二列是DependentMissingDescriptionCount 。 如果其值大于0，则应从DDL2GetDependentDescription列值复制并执行该语句。

For second situation, we have to run the T-SQL statement in DDL2GetDependentDescription column. Here is the value for that column still from second row with its results.

对于第二种情况，我们必须在DDL2GetDependentDescription列中运行T-SQL语句。 这是仍然来自第二行的该列的值及其结果。

If we move our horizontal cursor to the right, we will see the UpdateDML column. Those attributes without description can be documented using a modified version of the value from this column like we did for DDL2UpdateDescription column.

如果将水平光标向右移动，则会看到UpdateDML列。 可以使用此列中值的修改版本来记录那些没有描述的属性，就像我们对DDL2UpdateDescription列所做的那样 。

The review of already documented objects and attributes should be performed on regular basis but could be oriented based on the objects that were modified during development task. We could also add following columns and base our process on the value of these columns:

对已记录的对象和属性的检查应定期进行，但可以根据在开发任务期间修改的对象来确定。 我们还可以添加以下列，并基于这些列的值进行处理：

• LastReviewDate would store date and time of last review of the asset LastReviewDate将存储资产上次审查的日期和时间
• LastReviewVersion would store last version when the asset has been reviewed LastReviewVersion将存储最新版本
• LastReviewComments would store optional comments of the review LastReviewComments将存储评论的可选评论

We could only display attributes that have not been reviewed since for instance 6 months or 10 versions.

我们只能显示自6个月或10个版本以来尚未审核的属性。

将文档复制到扩展属性 (Copying documentation to Extended Properties)

Once we’ve completed documentation review steps, we could optionally apply this documentation to Extended Properties. Why? Because, as explained above, there are many tools (free or not) that use the MS_Description extended property as input to generate a read-only (or editable) version of your documentation in a transmittable format like HTML. Furthermore, SSMS users can access these properties and quickly get valuable information.

完成文档审查步骤后，我们可以选择将此文档应用于扩展属性。 为什么？ 因为，如上所述，有许多工具（无论是否使用）都使用MS_Description扩展属性作为输入来生成可传输格式（如HTML）的文档的只读（或可编辑）版本。 此外，SSMS用户可以访问这些属性并快速获取有价值的信息。

To do so, we will create a stored procedure called that will look at records from SchemaObjectDictionary and SchemaObjectsAttributes tables and call previously discussed Utils.ManageExtendedProperty stored procedure. We will call this new procedure [Docs].[ApplyDictionaryDefinitionsToExtendedProperties].

为此，我们将创建一个名为的存储过程，该存储过程将查看SchemaObjectDictionary和SchemaObjectsAttributes表中的记录，并调用先前讨论的Utils.ManageExtendedProperty存储过程。 我们将这个新过程称为[Docs]。[ApplyDictionaryDefinitionsToExtendedProperties] 。

The implementation of such a procedure is pretty trivial. It will store in a temporary table mandatory information for calls to Utils.ManageExtendedProperty stored procedure to succeed. This means it will scan for records that do not have a HasBeenDeleted column set to 1 and have a non-empty ObjectDescription or AttributeDescription column.

这样的过程的实现是很简单的。 它将在临时表中存储强制信息，以便成功调用Utils.ManageExtendedProperty存储过程。 这意味着它将扫描没有HasBeenDeleted列设置为1且具有非空ObjectDescription或AttributeDescription列的记录。

This is done using following query

这是使用以下查询完成的

 
SELECT 
    ObjectType ,
    DatabaseName,
    SchemaName,
    ObjectName,
    NULL as AttributeName,ObjectDescription, NULL
From Docs.SchemaObjectDictionary
WHERE 
        (@DatabaseName IS NULL OR DatabaseName = @DatabaseName)
    AND (@SchemaName IS NULL OR SchemaName = @SchemaName)
    AND ObjectDescription IS NOT NULL
    and hasBeenDeleted = 0
UNION ALL
SELECT     
    ObjectType,
    DatabaseName,
    SchemaName,
    ObjectName,
    AttributeName,
    AttributeDescription, 
CASE WHEN SUBSTRING(AttributeName,1,1) = '@' THEN 'PARAMETER' ELSE 'COLUMN' END
FROM Docs.SchemaObjectsAttributesDetails
WHERE 
    (@DatabaseName IS NULL OR DatabaseName = @DatabaseName)
AND (@SchemaName IS NULL OR SchemaName = @SchemaName)
AND AttributeDescription IS NOT NULL
AND DeletedAttribute= 0
 

Then it will loop on results from former query to call Utils.ManageExtendedProperty stored procedure.

然后，它将循环来自先前查询的结果，以调用Utils.ManageExtendedProperty存储过程。

设置文档解决方案 (Setup of documentation solution)

Attached to this article, you will find multiple files. They should be executed following this order:

本文所附，您将找到多个文件 。 它们应按照以下顺序执行：

1. CreateSchemas.sql
   CreateSchemas.sql
2. Table.Docs.SchemaObjectDictionary.sql
   Table.Docs.SchemaObjectDictionary.sql
3. Procedure.Docs.SchemaObjectDictionary_CRUD.sql
   Procedure.Docs.SchemaObjectDictionary_CRUD.sql
4. Table.Docs.SchemaObjectsAttributes.sql
   Table.Docs.SchemaObjectsAttributes.sql
5. Procedure.Docs.SchemaObjectsAttributes_CRUD.sql
   Procedure.Docs.SchemaObjectsAttributes_CRUD.sql
6. View.Docs.SchemaObjectDictionaryMissingDescription.sql
   View.Docs.SchemaObjectDictionaryMissingDescription.sql
7. View.Docs.SchemaObjectsAttributesDetails.sql
   View.Docs.SchemaObjectsAttributesDetails.sql
8. Procedure.Utils.ManageExtendedProperty.sql
   Procedure.Utils.ManageExtendedProperty.sql
9. Procedure.Docs.GetRelatedObjectDescriptions.sql
   Procedure.Docs.GetRelatedObjectDescriptions.sql
10. Type.Common.DatabaseNameList.sql
    Type.Common.DatabaseNameList.sql
11. Procedure.Common.RunQueryAcrossDatabases.sql
    Procedure.Common.RunQueryAcrossDatabases.sql
12. Procedure.Docs.PopulateSchemaObjectDictionary.sql
    Procedure.Docs.PopulateSchemaObjectDictionary.sql
13. Procedure.Docs.ApplyDictionaryDefinitionsToExtendedProperties.sql
    Procedure.Docs.ApplyDictionaryDefinitionsToExtendedProperties.sql

This order has been tested and was successful on a SQL Server 2012 instance.

该命令已经过测试，并在SQL Server 2012实例上成功完成。

使用第二个解决方案的具体示例的摘要 (Summary with a concrete example using second solution)

Testing object management

测试对象管理

Let’s create a table with defined extended property and check that it’s well added in dictionary tables.

让我们创建一个具有定义的扩展属性的表，并检查它是否在字典表中添加得很好。

 
CREATE SCHEMA Testing ;
GO
CREATE TABLE Testing.KeyValueTbl ( 
    KeyName VARCHAR(32), 
    KeyValue VARCHAR(1000)
);
 

Let’s now add a description to key value table:

现在让我们向键值表添加描述：

 
-- Adding property to table
EXEC sys.sp_addextendedproperty   
	@name = N'MS_Description',	@value = N'A key-value pair storage table.',   
	@level0type = N'SCHEMA',    @level0name = 'Testing',  
	@level1type = N'TABLE',     @level1name = 'KeyValueTbl';  
GO
 
 

And also to columns:

还有列：

 
-- Adding to columns
 
EXEC sp_addextendedproperty   
			@name		= N'MS_Description',@value = 'The key to use to get back value',  
			@level0type = N'Schema',		@level0name = 'Testing',  
			@level1type = N'Table',			@level1name = 'KeyValueTbl',   
			@level2type = N'Column',		@level2name = 'KeyName';  
GO  
 
EXEC sp_addextendedproperty   
			@name		= N'MS_Description',@value = 'The value corresponding to a given key',  
			@level0type = N'Schema',		@level0name = 'Testing',  
			@level1type = N'Table',			@level1name = 'KeyValueTbl',   
			@level2type = N'Column',		@level2name = 'KeyValue';  
GO
 

Let’s see what will happen if we run once again following query:

让我们看看如果再次执行以下查询将发生什么：

 
DECLARE @DbName SYSNAME;
SET @DbName = DB_NAME();
EXEC [Docs].[PopulateSchemaObjectDictionary] 
                    @DatabaseName = @DbName, 
                    @SchemaName   = 'Testing',
                    @Debug = 1
;
 

We see that it detected the new table as we get a report with one object found in the database (Sorry for the stored procedure not to be good at grammar).

我们看到它在数据库中找到一个对象的报告时检测到了新表（对不起，存储过程语法不好）。

And if we check in tables, we can see that comments have already been added in ObjectDescription column:

而且，如果我们检入表，可以看到在ObjectDescription列中已经添加了注释：

Now, let’s say we rename the KeyValue column to Value4Key using following T-SQL statement.

现在，假设我们使用以下T-SQL语句将KeyValue列重命名为Value4Key。

 
EXEC sp_rename	@objname = 'Testing.KeyValueTbl.KeyValue',
				@NewName = 'Value4Key', 
				@ObjType = 'COLUMN'
;
 

Notice that we get a warning message:

请注意，我们收到警告消息：

Let’s run again the population procedure and see what will happen…

让我们再次运行人口程序，看看会发生什么……

First, as expected, nothing has changed for the record describing table, but changes are visible in SchemaObjectsAttributes as you can see below:

首先，正如预期的那样，记录描述表没有任何更改，但是更改在SchemaObjectsAttributes中可见，如下所示：

We see that record corresponding to KeyValue column has been marked as deleted and that a new record corresponding to Value4Key has been added. Now it’s up to you to decide whether to complete ReplacementDefinitionId column for deleted record or simply delete the row.

我们看到对应于KeyValue列的记录已被标记为已删除，并且已添加对应于Value4Key的新记录。 现在由您决定是为已删除的记录完成ReplacementDefinitionId列，还是仅删除该行。

If we look at extended properties corresponding to the table, we get this:

如果我们查看与表相对应的扩展属性，则会得到以下信息：

Now let’s drop the table.

现在让我们放下桌子。

 
DROP TABLE Testing.KeyValueTbl
 

If we run again a query against sys.extended_properties table, we’ll get no results, meaning that an accidental drop of that table would lead to the loss of documentation about that table too.

如果再次对sys.extended_properties表运行查询，将不会获得任何结果，这意味着该表的意外删除也将导致该表的文档丢失。

Let’s see what happens when we run again the population stored procedure… First of all, it did not detect the table KeyValueTbl table as it has been dropped.

让我们看看再次运行填充存储过程时会发生什么……首先，它没有检测到表KeyValueTbl表，因为它已被删除。

But we haven’t lost documentation so far: we can see that everything has been marked as deleted but it’s up to the user to decide the appropriate action.

但是到目前为止，我们还没有丢失任何文档：我们可以看到所有内容都被标记为已删除，但是由用户决定采取适当的措施。

Testing SQL Server object documentation

测试SQL Server对象文档

Now let’s say that we want to document the test SQL database in which we ran queries so far. To do so, we will first run population stored procedure without value for its @SchemaName parameter, then we will use the SchemaObjectDictionaryMissingDescription view.

现在，我们要记录到目前为止已经在其中运行查询的测试SQL数据库。 为此，我们将首先运行填充存储过程，该存储过程的@SchemaName参数没有值，然后使用SchemaObjectDictionaryMissingDescription视图。

The previous figure is a sample screen capture with first line returned by previous view corresponding to RunQueryAcrossDatabases stored procedure. We see that the object has a missing description so as eight of its parameters.

上图是一个示例屏幕截图，其中上一行视图的第一行与RunQueryAcrossDatabases存储过程相对应。 我们看到该对象缺少描述，因此缺少八个参数。

Here is an example of value for DDL2UpdateDescription column:

这是DDL2UpdateDescription列的值示例：

 
EXEC [Docs].[SchemaObjectDictionary_Upsert]
                 @DefinitionId           = 26,
                 @ServerId               = NULL,
                 @DatabaseName           = 'TestSchemaDoc',
                 @SchemaName             = 'Common',
                 @ObjectName             = 'DatabaseNameList',
                 @ObjectType             = 'TABLE TYPE',
                 @ObjectDescription      = 'TODO:YOUR_DESCRIPTION',
                 @RelatedApplicationName = NULL,
                 @RelatedModuleName      = NULL,
                 @isDepreciated          = 0,
                 @hasBeenDeleted         = 0,
                 @DeletionDiscoveryDate  = NULL,
                 @DeletionVersionFrom    = NULL,
                 @ReplacementObjectId    = NULL
;
 

As we can see, everything is ready to update record. We just need to change TODO: YOUR_DESCRIPTION with something else.

如我们所见，一切准备就绪，可以更新记录。 我们只需要更改TODO ： YOUR_DESCRIPTION即可 。

Let’s say we modify previous statement to the following one:

假设我们将前面的语句修改为以下语句：

 
EXEC [Docs].[SchemaObjectDictionary_Upsert]
                 @DefinitionId           = 26,
                 @ServerId               = NULL,
                 @DatabaseName           = 'TestSchemaDoc',
                 @SchemaName             = 'Common',
                 @ObjectName             = 'DatabaseNameList',
                 @ObjectType             = 'TABLE TYPE',
                 @ObjectDescription      = 'A list of database names',
                 @RelatedApplicationName = 'Common Database',
                 @RelatedModuleName      = 'Core',
                 @isDepreciated          = 0,
                 @hasBeenDeleted         = 0,
                 @DeletionDiscoveryDate  = NULL,
                 @DeletionVersionFrom    = NULL,
                 @ReplacementObjectId    = NULL
;
 

We should do the same for every object appearing with MissingDescription column with a value of 1.

我们应该对出现在MissingDescription列中且值为1的每个对象执行相同的操作。

By the way, if an object is still in the list with a value of 0 for that column, it means that we should consider DDL2GetDependentDescription column.

顺便说一句，如果该列中的某个对象仍在列表中且值为0，则意味着我们应该考虑DDL2GetDependentDescription列。

You will find below the value of that column for RunQueryAcrossDatabases stored procedure.

您将在该列的值下面找到RunQueryAcrossDatabases存储过程。

 
EXEC [Docs].[GetRelatedObjectDescriptions]
                 @ServerId               = NULL,
                 @DatabaseName           = 'TestSchemaDoc',
                 @SchemaName             = 'Common',
                 @ObjectName             = 'RunQueryAcrossDatabases'
;
 

Here is a sample result of what we get when we run the previous statement:

这是运行上一条语句时得到的示例结果：

As you can see, there are not only details we can get from SchemaObjectsAttributesDetails view but also an UpdateDML column. This column contains, for each record, an almost ready to use call to a DataObjectColumnDictionary_Upsert stored procedure as shown in following example:

如您所见，不仅可以从SchemaObjectsAttributesDetails视图中获取详细信息，而且还包含UpdateDML列。 对于每条记录，此列包含对DataObjectColumnDictionary_Upsert存储过程的几乎随时可以使用的调用，如以下示例所示：

 
EXEC [Docs].[SchemaObjectsAttributes_Upsert]
           @DefinitionId            = 140,
           @ObjectDictionaryId      = 25,
           @AttributeName           = '@database_list',
           @AttributeDescription    = 'TODO:ADD_YOUR_DESCRIPTION',
           @HasBeenDeleted          = 0,
           @DeletionDiscoveryDate   = NULL,
           @DeletionVersionFrom     = NULL,
           @ReplacementDefinitionId = NULL
;
 

Let’s modify previous statement to following one and run it.

让我们将前一条语句修改为后一条并运行它。

 
EXEC [Docs].[SchemaObjectsAttributes_Upsert]
           @DefinitionId            = 140,
           @ObjectDictionaryId      = 25,
           @AttributeName           = '@database_list',
           @AttributeDescription    = 'A list of databases',
           @HasBeenDeleted          = 0,
           @DeletionDiscoveryDate   = NULL,
           @DeletionVersionFrom     = NULL,
           @ReplacementDefinitionId = NULL
;
 

Once it’s done, let’s run again the query against SchemaObjectDictionaryMissingDescription view and we can see that the DependentMissingDescriptionCount has a value of 7 instead of 8 previously.

完成后，让我们再次对SchemaObjectDictionaryMissingDescription视图运行查询，我们可以看到DependentMissingDescriptionCount的值为7，而不是以前的8。

最后的话 (Final word)

In this article, we’ve seen that we could use a built-in documentation feature called Extended Properties and we even extended it with a stored procedure. While this feature seems good, there are problems to it like the persistence of documentation after object deletion. That’s the reason why we defined a homemade solution we could use in common cases (some additional work should be done to consider all possible database objects) and that can interact with Extended Properties so that 3rd-party tools can also use our documentation.

在本文中，我们已经看到我们可以使用称为扩展属性的内置文档功能，甚至可以使用存储过程对其进行扩展。 虽然此功能看起来不错，但它存在一些问题，例如删除对象后文档的持久性。 这就是为什么我们定义一种自制解决方案的原因，我们可以在常见情况下使用该解决方案（应该做一些额外的工作来考虑所有可能的数据库对象），并且可以与扩展属性进行交互，以便第三方工具也可以使用我们的文档。

资料下载 (Downloads)

• Article scripts文章脚本

参考资料 (References)

• sp_addextendedproperty (Transact-SQL) sp_addextendedproperty（Transact-SQL）
• System Information Schema Views (Transact-SQL) 系统信息架构视图（Transact-SQL）
• How do you document your databases? 您如何记录数据库？
• Using Extended Properties on Database Objects 在数据库对象上使用扩展属性
• Viewing Extended Properties 查看扩展属性

翻译自: https://www.sqlshack.com/how-to-document-sql-server-database-objects/