记录MS SQL Server数据库

目录

介绍

如何使用文档工具记录SQL Server数据库

使用dbForge Documenter for SQL Server 记录数据库

结论

介绍

所有公司都会积累各种数据，而这些数据最终不可避免地会存储在数据库中。如今，您能想到的几乎所有应用程序都包含一个数据库。这就是为什么对数据库对象描述有相当高的需求。

有两种主要方法可以处理此过程——您可以使用对象的扩展属性或各种第三方实用程序。

在本文中，我们将回顾一些使用扩展属性和第三方实用程序的SQL Server数据库文档示例。

如何使用文档工具记录SQL Server数据库

您可以使用系统存储过程sp_addextendedproperty添加扩展属性。其文档位于此处。

以下是如何使用此存储过程的一些示例：

1、为dbo.GetPlansObject函数的@ObjectID参数添加了描述：

SELECT emp.[EmployeeID]
   ,emp.[LastName]
   ,emp.[FirstName]
   ,s.[SkillName]
   ,DATEDIFF(DAY, jh.[StartDate], _
    jh.[FinishDate]) / (DATEDIFF(YEAR, jh.[StartDate], _
    jh.[FinishDate]) + 1) AS [PeriodDay]
   ,CASE
WHEN ((2017 >= year(jh.[StartDate])) AND
(2018 > year(jh.[FinishDate]))) THEN 2017
WHEN ((2018 >= year(jh.[StartDate])) AND
(2019 > year(jh.[FinishDate]))) THEN 2018
WHEN ((2019 >= year(jh.[StartDate])) AND
(2020 > COALESCE(year(jh.[FinishDate]), year(GetDate())))) THEN 2019
END AS [Year]
FROM [Employee] AS emp
INNER JOIN [JobHistory] AS jh
ON emp.[EmployeeID] = jh.[EmployeeID]
INNER JOIN [Project] AS p
ON p.[ProjectID] = jh.[ProjectID]
INNER JOIN [ProjectSkill] AS ps
ON p.[ProjectID] = ps.[ProjectID]
INNER JOIN [Skill] AS s
ON s.[SkillID] = ps.[SkillID]
WHERE (jh.[FinishDate] >= DATEADD(YEAR, -3, GetDate())
OR (jh.[FinishDate] IS NULL));

同样，您可以为存储过程参数添加描述。

2、为dbo.GetPlansObject函数添加说明：

EXEC sys.sp_addextendedproperty @name=N'MS_Description',

@value=N'Returns all plans for the specified object',
@level0type=N'SCHEMA',
@level0name=N'dbo',
@level1type=N'FUNCTION',
@level1name=N'GetPlansObject';

您可以以类似的方式为存储过程和触发器添加描述。

3、为inf.vColumnTableDescription视图添加描述：

EXEC sys.sp_addextendedproperty @name=N'MS_Description',

@value=N'Description of table columns',
@level0type=N'SCHEMA',
@level0name=N'inf',
@level1type=N'VIEW',
@level1name=N'vColumnTableDescription';

您还可以为表添加描述。

4、为dbo.TABLE table’s TEST_GUID列添加说明：

EXEC sys.sp_addextendedproperty @name=N'MS_Description',

@value=N'Record ID (global)',
@level0type=N'SCHEMA',
@level0name=N'dbo',
@level1type=N'TABLE',
@level1name=N'TEST',
@level2type=N'COLUMN',
@level2name=N'TEST_GUID';

还可以添加表列的描述。

5、为rep方案添加说明：

EXEC sys.sp_addextendedproperty @name=N'MS_Description',

@value=N'The rep schema objects contain information for reports' ,
@level0type=N'SCHEMA',
@level0name=N'rep';

6、添加数据库描述：

EXEC sys.sp_addextendedproperty @name=N'MS_Description',

@value=N'Administration Database
Version for MS SQL Server 2016-2017 
(MS SQL Server 2012-2014 is also fully or partially supported).
Support for all versions up to MS SQL Server 2012 
may not be at a sufficient level for use in a production environment';

7、添加数据库索引的描述：

EXEC sys.sp_addextendedproperty @name=N'MS_Description',
								@value=N'cluster index' ,
								@level0type=N'SCHEMA',
								@level0name=N'srv',
								@level1type=N'TABLE',
								@level1name=N'Recipient',
								@level2type=N'INDEX',
	 @level2name=N'indInsertUTCDate';

同样，您可以为视图索引添加描述。

为了更改或删除描述，您只需要分别使用sp_updateextendedproperty和sp_dropextendedproperty存储过程。您可以在文档中阅读有关这些存储过程的更多信息：

• • sp_updateextended属性
  • sp_dropextended属性

现在，我们将分析获取对象描述信息的方式：

1、要获取有关数据库对象描述的信息，可以使用以下查询：

select

SCHEMA_NAME(obj.[schema_id]) as SchemaName
,QUOTENAME(object_schema_name(obj.[object_id]))+'.'+quotename(obj.[name]) as ObjectName
,obj.[type] as [Type]
,obj.[type_desc] as [TypeDesc]
,ep.[value] as ObjectDescription
from sys.objects as obj
left outer join sys.extended_properties as ep on obj.[object_id]=ep.[major_id]
											 and ep.[minor_id]=0
											 and ep.[name]='MS_Description'
where obj.[is_ms_shipped]=0
and obj.[parent_object_id]=0

此处使用以下系统视图：

1. sys.objects——数据库对象。您可以在此处了解更多信息。
2. sys.extended_properties——当前数据库中的扩展属性。更多细节在这里。

此查询产生以下列：

1. SchemaName——对象模式
2. ObjectName——对象的名称
3. Type——对象类型
4. TypeDesc——对象类型的描述
5. ObjectDescription——自定义对象描述

2、要获取具有父项的对象的描述，可以使用以下查询：

select

SCHEMA_NAME(obj.[schema_id]) as SchemaName
,QUOTENAME(object_schema_name(obj.[parent_object_id]))+_
'.'+quotename(object_name(obj.[parent_object_id])) as ParentObjectName
,QUOTENAME(object_schema_name(obj.[object_id]))+'.'+quotename(obj.[name]) as ObjectName
,obj.[type] as [Type]
,obj.[type_desc] as [TypeDesc]
,ep.[value] as ObjectDescription
from sys.all_objects as obj
left outer join sys.extended_properties as ep on obj.[parent_object_id]=ep.[major_id]
											 and ep.[minor_id]=obj.[object_id]
											 and ep.[name]='MS_Description'
where obj.[is_ms_shipped]=0
and obj.[parent_object_id]0

此处将显示与先前查询中的列相似的列，但添加了一个新列。ParentObjectName是对象的父级（例如，表是列的父级）。

此查询还使用sys.all_objects显示所有数据库对象的系统视图。您可以在此处阅读有关此视图的更多信息。

3、您可以通过执行以下查询来获取参数描述：

select

SCHEMA_NAME(obj.[schema_id]) as SchemaName
,QUOTENAME(object_schema_name(obj.[object_id]))+_
'.'+quotename(object_name(obj.[object_id])) as ParentObjectName
,p.[name] as ParameterName
,obj.[type] as [Type]
,obj.[type_desc] as [TypeDesc]
,ep.[value] as ParameterDescription
from sys.parameters as p
inner join sys.objects as obj on p.[object_id]=obj.[object_id]
left outer join sys.extended_properties as ep on obj.[object_id]=ep.[major_id]
											 and ep.[minor_id]=p.[parameter_id]
											 and ep.[name]='MS_Description'
where obj.[is_ms_shipped]=0

此处显示的字段与上一个查询中的字段相似。但是，有许多更改和添加：

1. Type和TypeDesc——与父对象（存储过程或函数）相关
2. ParameterName——参数名称

此查询还使用sys.parameters显示数据库对象参数的系统视图。有关更详细的描述，请随时参考视图的文档。

4、可以通过以下查询获得表列的描述：

select

SCHEMA_NAME(t.schema_id) as SchemaName
,QUOTENAME(object_schema_name(t.[object_id]))+'.'+quotename(t.[name]) as TableName
,c.[name] as ColumnName
,ep.[value] as ColumnDescription
from sys.tables as t
inner join sys.columns as c on c.[object_id]=t.[object_id]
left outer join sys.extended_properties as ep on t.[object_id]=ep.[major_id]
											 and ep.[minor_id]=c.[column_id]
											 and ep.[name]='MS_Description'
where t.[is_ms_shipped]=0;

此处显示以下列：

1. SchemaName——表模式的名称
2. TableName——表的名称
3. ColumnName——表列的名称
4. ColumnDescription——表格列的描述

该查询还使用以下系统视图：

1. sys.tables——表。更多细节在这里
2. sys.columns——列。在此处查找更多信息

5、可以通过以下查询检索视图列的描述：

select

SCHEMA_NAME(t.schema_id) as SchemaName
,QUOTENAME(object_schema_name(t.[object_id]))+'.'+quotename(t.[name]) as ViewName
,c.[name] as ColumnName
,ep.[value] as ColumnDescription
from sys.views as t
inner join sys.columns as c on c.[object_id]=t.[object_id]
left outer join sys.extended_properties as ep on t.[object_id]=ep.[major_id]
											 and ep.[minor_id]=c.[column_id]
											 and ep.[name]='MS_Description'
where t.[is_ms_shipped]=0;

此处显示以下列：

1. SchemaName——演示方案名称
2. ViewName——视图名称
3. ColumnName——视图列的名称
4. ColumnDescription——视图列的描述

该请求还使用sys.views系统视图，该视图显示所有数据库视图。您可以在此处找到更多信息。

6、您可以使用以下查询获取数据库架构描述：

select

SCHEMA_NAME(t.schema_id) as SchemaName
,ep.[value] as SchemaDescription
from sys.schemas as t
left outer join sys.extended_properties as ep on t.[schema_id]=ep.[major_id]
											 and ep.[minor_id]=0
											 and ep.[name]='MS_Description'

有两列SchemaName和SchemaDescription，分别显示模式的名称和描述。

该查询还使用sys.schemas系统视图，该视图显示所有数据库架构。更多详情。

7、您可以使用以下查询获取整个数据库中索引的所有扩展属性：

SELECT SCHEMA_NAME(obj.[schema_id]) as [SchemaName],
	   obj.[name] as [ObjectName],
	   ind.[name] as [IndexName],
	   EP.[name]  as [ExtendedPropertyName],
	   EP.[value] as [ExtendedPropertyValue]
FROM sys.extended_properties AS EP
inner join sys.objects as obj on EP.[major_id]=obj.[object_id]
inner join sys.indexes as ind on EP.[minor_id]=ind.[index_id]
WHERE EP.class = 7

此处显示以下列：

1. SchemaName——对象模式的名称
2. ObjectName——对象名称
3. IndexName——索引名称
4. ExtendedPropertyName——扩展属性的名称
5. ExtendedPropertyValue——扩展属性的值

该查询还使用sys.indexes系统视图，该视图显示数据库中的所有索引。您可以在此处找到更多详细信息。

接下来，我们将看看如何使用Devart的专用实用程序以另一种方式处理数据库文档。

使用dbForge Documenter for SQL Server 记录数据库

在dbForge Studio for SQL Server中，您可以创建一个数据库文档项目。此功能也在dbForge Documenter for SQL Server中实现。

在本例中，我们将使用专为MS SQL Server DBMS维护设计的SRV数据库。它可以出于任何目的免费分发，因此您可以在此处下载其源文件：Projects-MS-SQL-Server-DBA/SRV at master · jobgemws/Projects-MS-SQL-Server-DBA · GitHub。

要在打开Studio后创建数据库文档项目，请单击“工具”菜单中的“新建文档... ” ：

图 1. 为数据库创建新文档

接下来，选择您需要为其创建数据库文档的所需服务器。

之后，将出现以下文档主页。它可以以多种方式排列，包括以下内容：

图 2. 自定义主页

您无需在文档中生成主页即可开始。为此，只需取消选中窗口左上角的框即可。

请记住，只会生成启用了复选框的元素。

接下来，您需要在所有选定服务器的范围内以及为每个特定服务器配置生成：

图 3. 为所有选定的服务器设置文档生成

例如，让我们通过将所有选项切换为OFF来禁用此页面上的所有设置：

图 4. 为所选服务器设置文档生成

接下来，选择“用户数据库”页面。另外，为了这个例子，让我们关闭“用户数据库”选项：

图 5. 设置“用户数据库”页面

接下来，选择 SRV 数据库：

图 6. 设置SRV数据库页面

在这里，我们将输入数据库的描述，然后单击“保存”。

用户保存的所有描述更改也将保存在相应对象的高级属性中。

之后，我们将关闭“属性”、“选项”和“数据库文件”：

图 7. 禁用 SRV 数据库选项

其余设置应保持启用状态：

图 8. 查看启用的SRV数据库选项

现在让我们打开SRV数据库本身并选择“表”页面：

图 9. 查看和编辑SRV表描述

此页面列出了表格及其说明。

编辑说明时，应出现两个按钮：

1. 保存——保存更改
2. 取消——取消更改

让我们通过单击来选择dbo.AuditQuery表格：

图 10. 查看和编辑dbo.AuditQuery表的列描述

此页面列出了表格的列及其说明。

此外，您还可以看到此页面上显示的表索引。

编辑说明时，应出现两个按钮：

1. 保存——保存更改
2. 取消——取消更改

此外，该页面还包含表定义代码、表描述、表属性等。

如果有必要，您可以关闭任何单个选项。

Views文件夹中的视图页面以及其他数据库对象的页面看起来都一样：

图 11. 数据库对象类型

通过这种方式，您可以记录以下数据库对象：

1. 表及其列和索引
2. 视图及其列和索引
3. 存储过程及其参数
4. 函数及其参数，包括表格和标量
5. DDL触发器
6. 用户
7. 角色
8. 模式和其他

设置文档后，您可以通过单击“保存”并选择适当的路径和文件来保存项目：

图 12. 保存文档项目

生成数据库文档

现在，让我们仔细看看文档项目的顶部面板：

图 13. 查看文档项目菜单

这里有三个按钮：

1. 添加连接... ——允许您为文档添加新服务器
2. 刷新——开始更新所选文档服务器上的信息的过程
3. 生成...——打开文档生成设置窗口。

单击“生成...”按钮。文档生成窗口本身将打开：

图 14. 设置文档生成

在这里，您可以选择许多不同的选项，但以下选项是必需的：

1. 格式（通常，默认选择HTML）
2. 将生成文档的文件夹

此外，通过单击左下角的“保存命令行... ”，可以将所选设置生成为.bat文件。

接下来，您需要单击“生成”按钮以开始生成文档本身的过程。此操作成功完成后，将出现以下窗口：

图 15. 成功完成文档生成

之后，转到选定的文档目录并打开mail.html文件以打开生成的SRV数据库Web文档。

同样，可以在一个文档中收集多个数据库，即使它们位于不同的服务器上。

结论

我们研究了如何通过高级属性以及在dbForge Documenter for SQL Server的帮助下创建和查看对象描述。描述构成了数据库文档的大部分。此外，从本文所示的示例中，我们可以看到dbForge Documenter for SQL Server工具允许您快速创建和编辑特定对象和整个文档的描述。这可以为单个或多个数据库完成——无论它们是位于一台服务器上还是位于多台不同的服务器上。

https://www.codeproject.com/Articles/5161784/Documenting-MS-SQL-Server-Databases