记录MS SQL Server数据库

àì夳堔傛蜴生んèń 2022-09-17 04:27 216阅读 0赞

**目录**

介绍

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

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

结论

--------------------

# 介绍 #

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

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

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

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

您可以使用系统存储过程sp\_addextendedproperty添加扩展属性。其文档位于[此处][Link 1]。

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

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_updateextended]
     *  [sp\_dropextended属性][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——数据库对象。您可以[在此处][Link 2]了解更多信息。
2.  sys.extended\_properties——当前数据库中的扩展属性。更多细节[在这里][Link 3]。

此查询产生以下列：

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显示所有数据库对象的系统视图。您可以[在此处][Link 4]阅读有关此视图的更多信息。

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显示数据库对象参数的系统视图。有关更详细的描述，请随时参考[视图的文档][Link 5]。

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——表。更多细节[在这里][Link 6]
2.  sys.columns——列。[在此处][Link 7]查找更多信息

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系统视图，该视图显示所有数据库视图。您可以[在此处][Link 8]找到更多信息。

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系统视图，该视图显示所有数据库架构。[更多详情][Link 9]。

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系统视图，该视图显示数据库中的所有索引。您可以[在此处][Link 10]找到更多详细信息。

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

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

在[dbForge Studio for SQL Server中][dbForge Studio for SQL Server]，您可以创建一个数据库文档项目。此功能也在[dbForge Documenter 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][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][https_www.codeproject.com_Articles_5161784_Documenting-MS-SQL-Server-Databases]

[Link 1]: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-addextendedproperty-transact-sql?view=sql-server-2017
[sp_updateextended]: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-updateextendedproperty-transact-sql?view=sql-server-2017
[sp_dropextended]: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-dropextendedproperty-transact-sql?view=sql-server-2017
[Link 2]: https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-objects-transact-sql?view=sql-server-2017
[Link 3]: https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/extended-properties-catalog-views-sys-extended-properties?view=sql-server-2017
[Link 4]: https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-all-objects-transact-sql?view=sql-server-2017
[Link 5]: https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-parameters-transact-sql?view=sql-server-2017
[Link 6]: https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-tables-transact-sql?view=sql-server-2017
[Link 7]: https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-columns-transact-sql?view=sql-server-2017
[Link 8]: https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-views-transact-sql?view=sql-server-2017
[Link 9]: https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/schemas-catalog-views-sys-schemas?view=sql-server-2017
[Link 10]: https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-indexes-transact-sql?view=sql-server-2017
[dbForge Studio for SQL Server]: https://www.devart.com/dbforge/sql/studio/?utm_source=codeproject.com&utm_medium=referral&utm_campaign=articlemssqldocum
[dbForge Documenter for SQL Server]: https://www.devart.com/dbforge/sql/documenter/?utm_source=codeproject.com&utm_medium=referral&utm_campaign=articlemssqldocum
[Projects-MS-SQL-Server-DBA_SRV at master _ jobgemws_Projects-MS-SQL-Server-DBA _ GitHub]: https://github.com/jobgemws/Projects-MS-SQL-Server-DBA/tree/master/SRV
[https_www.codeproject.com_Articles_5161784_Documenting-MS-SQL-Server-Databases]: https://www.codeproject.com/Articles/5161784/Documenting-MS-SQL-Server-Databases