YADDU。另一个（MS-SQL）数据库文档实用程序

• 下载源代码和演示 - 29.34 KB

引言

在 2005 年之前的 MS-SQL 版本中，没有可编程的方法来添加额外信息，例如表或列的描述。解决此问题通常会导致解决方案，这些解决方案要么向每个数据库添加一个或多个信息表，要么将信息保存在完全单独的存储中。无论哪种情况，额外的​​信息很容易与父数据库不同步。MS-SQL 2005 中引入的数据库对象的扩展属性以及用于操作它们的存储过程的提供，在很大程度上解决了这个问题。

本文介绍了一些非常简单的类，用于使用 MS-SQL 数据库的扩展过程以及一个利用它们的精简的 Web UI 来提取、格式化和更新数据字典信息。它是基于我大约 6 年前写的实用程序的最近重构。原始版本受限于必须使用 SQL-7，并 suffered 了上述一些问题。

驱动原始版本开发的​​主要需求是提供

• 提供更易读的 sp_help 信息摘要，最好是基于浏览器的
• 允许维护表列的注释/描述，同上。

如所示，此实用程序允许维护以下信息

表格

隐藏

- 将表从文档实用程序中隐藏。

版本

- 表引入的数据库版本

注释

- 表用途的描述，

Column

注释

- 列用途的描述，

背景

SQL Server 扩展属性

SQL2K5 引入的扩展属性允许您将包含最多 7,500 字节数据的 sql_variant 值与 MS-SQL 服务器数据库中的对象关联。

扩展属性有许多优点。特别是它们非常适合数据字典应用程序：

• 它们是数据库的一部分，随数据库一起备份和还原。
• 当父对象（表、视图、列）被删除时自动删除。
• 可以使用 SQL Server Management Studio 查看

扩展属性使用三个存储过程进行维护

• sys.sp_addextendedproperty
• sys.sp_updateextendedproperty
• sys.sp_deleteextendedproperty

不幸的是，这些存储过程的行为有点“不友好”，在调用它们之前，有必要检查您要添加、更新或删除的属性是否存在。

information_schema 和其他视图

这些视图允许我们检查数据库结构，而无需像早期版本的 MS SQL Server 中的 sysobjects 表那样。对于本文，我们感兴趣的是以下内容：

sys.extended_properties

- 列出当前数据库中所有已定义的扩展属性。

sys.information_schema.tables

- 列出当前数据库中的所有表和视图。

sys.information_schema.columns

- 列出当前数据库中视图和表的列信息。

sys.information_schema.table_constraints

- 列出列和表的约束，例如主键。

sys.views

- 列出数据库中的视图。允许我们在 information_schema.tables 中区分视图和表，并检索表、视图和列名称的对象 ID。

sys.tables

- 列出数据库中的表。作用与 sys.views 相同。

T-SQL 中的 XML

现在这已经是老生常谈了，但它允许我们使用一些简单的 XSLT 生成各种输出格式。

Using the Code

类：Query

它有几个返回 T-SQL 脚本的静态方法。T-SQL 嵌入在应用程序中，因为我们不希望在数据库中添加任何内容，以便任何我们可能编写的应用程序都能运行。

方法	范围	Returns
GetSchemata	public static	string 获取一个或多个表架构所需的 T-SQL。
GetSingleTable	public static	string 获取单个表架构所需的 T-SQL。基本上是对 GetSchemata 的调用转发。
ColumnList	public static	string 获取单个表列列表所需的 T-SQL。
UpdateColumnProperty	public static	string 添加或更新表列扩展属性所需的 T-SQL。
UpdateTableProperty	public static	string 添加或更新表或视图扩展属性所需的 T-SQL。

这些方法所代表的脚本，特别是 GetSchemata，决定了我们创建和更新的扩展属性。

GetSchemata

对于表和列的属性，我们使用几个派生表从 sys.extended_properties 视图中提取扩展属性。如果您想维护更多扩展属性，您可以从这里开始修改。如果您愿意，扩展属性的数量和名称可以设置为可配置的。

表属性

  ...
  -- Pivot extended properties for the table.
  left  join 
      (select 
        major_id,
        max(case when [name]='comment' then [value] else '' end) as comment,
        max(case when [name]='hidden'  then [value] else '0' end) as hidden,
        max(case when [name]='version' then [value] else '' end) as version
        from sys.extended_properties
        where class = 1 and minor_id = 0
        group by major_id
      ) as [tableProps]
    ...

列属性

 ...
  -- Pivot extended properties for the column.
  left  join 
      (select 
        major_id, minor_id,
        max(case when [name]='comment' then [value] else '' end) as comment,
        max(case when [name]='version' then [value] else '' end) as version
        from sys.extended_properties
        where class = 1 
        group by major_id, minor_id
      ) as [columnProps]
  ...

值得注意的是，列通过与其父表相同的 major_id 和一个 minor_id 来标识。minor_id 只是列的索引。

GetSchemata 的输出是零个或多个 XML 格式片段行。这些片段被连接起来形成一个如下所示的 XML 片段。这允许我们使用 XSL 生成许多不同的输出格式。

  <Tables Name="lbs">
  <table Name="Bike" IsView="0">
    <tableProps Comment="Description of a specific bike linking to owner and manufacturer." 
                Hidden="0" 
                Version="1.0.0">
      <column Name="ID" PrimaryKey="1" Type="uniqueidentifier" Length="16" AllowNull="0" DefaultValue="newid()">
        <columnProps Comment="Internal Primary Key GUID" 
                     Version=""/>
      </column>
      <column Name="CustomerID" PrimaryKey="0" Type="uniqueidentifier" Length="16" AllowNull="1">
        <columnProps Comment="Owner ID. If bike not stock" 
                     Version=""/>
      </column>

      :
      :
      :
      :

    </tableProps>
    </table>

    :
    :

  </Tables>

UpdateColumnProperty, UpdateTableProperty

据我所知，扩展属性没有批量更新方法。每次添加、删除或更新都需要调用存储过程。由于数据字典实用程序不太可能产生大量的更新（假设大多数表很少有十几个列，并且用户一次只能更新一个表），这不太可能导致严重的性能问题。

这些方法返回的 T-SQL 是直接的，尽管可能不好看。一个 select 语句用于确定属性是否已与表或列关联，从而根据需要调用 add 或 update 存储过程。

您会注意到，拥有该表的架构被硬编码为“dbo”。这对于我在家玩弄的数据库是合适的。如果您采用此代码，您可能希望从 sys.schemas 视图中检索架构名称。

获取表的架构名称

    select 
      sys.schemas.name as [schemaName]
    from sys.schemas 
     inner join (select [name], schema_id from sys.tables
                 union select [name], schema_id from sys.views)
                 as systable
          on   systable.schema_id = sys.schemas.schema_id
          and  systable.name = 'Bike'

类：XML

它只是封装了将 XSL 转换应用于 XML 字符串所需的调用。

方法	范围	Returns	注释
ApplyTransform	public static	字符串	返回包含转换输出的字符串。
ApplyTransform	public static	void	将转换输出写入文件。

XSLT 模板

作为演示，包含了五个模板。

文件名	输出	注释
ToHTML.xsl	生成一个简单的 HTML 页面，提供类似 sp_help 的表描述。	提供一个链接到 ASPX 页面，用户可以在其中更新表的扩展属性。
ToDOC.xsl	一种用于 Open Office Writer 或 MS Word 的“文档”格式。	这是一个技巧。输出是 ToHTML 模板的一个简化变体，带有一个 doctype 标签，允许 Writer 或 Word 将“文档”作为格式化文本打开。它节省了大量创建 RTF 的繁重工作，并且由于 Writer 和 MS Word 都允许将文档保存为多种格式（包括 PDF），因此可以巧妙地解决向第三方提供只读数据库文档副本的问题。
ToCSharp.xsl	一个 C# 类，提供对表名、其列名、数据长度和每个列的 SQL 数据类型的访问。	现在生成数据库对象的模型变得如此容易，这个类不如 Framework 1.1 中有用，但对于处理 DataTables 和 DataViews 仍然很方便。
ToVB.xsl	ToCSharp.xsl 的 VB 等效版本	与 C# 输出相同。
ToSQL.xsl	一个 T-SQL 脚本，用于向表添加所有定义的扩展属性。	旨在用于在创建新版本的数据库而尚未设置任何扩展属性时进行批量设置数据。

每个模板都遵循相同的极其简单的模式

    <!-- Match root -->
    <xsl:template match="/">

      <!-- Insert/generate any document "header". -->
           
      <!-- Process each table in alphabetic order -->
      <xsl:apply-templates select="Tables/table">
        <xsl:sort select="Name"/>
      </xsl:apply-templates>

    </xsl:template>

    <!-- Deal with a single table -->
    <xsl:template match="table">

      <!-- Lay out table specific information.  -->

      <!-- Process each column -->
      <xsl:apply-templates select="tableProps/column"/>

    </xsl:template>

    <xsl:template match="tableProps/column">
      <!-- Lay out a single column  --> 
    </xsl:template>

    </xsl:stylesheet>

LINQ for XML - XElement 扩展类

允许用户更新表扩展属性的 ASPX 页面是使用 Query::GetSingleTable 的输出和 LINQ for XML 动态生成的。这相当直接，除了一个小烦恼；如果命名属性没有值，XElement::Attribute 方法会抛出 null 异常。为了使代码更易于理解，发现有必要创建一些 XElement 扩展方法。

方法	注释
AttributeString	返回命名属性的字符串值。如果未提供备用默认值，则为空字符串。
AttributeBool	返回命名属性的布尔值。如果未提供备用默认值，则为 False。
AttributeValue	返回命名属性的对象。调用时必须提供默认值。

UI 注释

UI 非常简单。它只有 4 个主要组件

文件名	注释
default.aspx	一个登录页面。
sp_help	显示所有选定表的架构。此页面没有 aspx 或其他文件与之关联。它按需创建。
dataDic.aspx	允许更新单个表的数据字典信息。
loadSchema.aspx	没有可见元素。接受来自 default.aspx 的请求并生成所需的输出。

dataDic.aspx

此页面获取一个表的 XML 描述，并使用 LINQ for XML 为与该表关联的每个扩展属性创建 aspx 文本控件。按当前编写，它只期望为上面给出的属性创建控件和检索信息。将其修改为处理可配置的扩展属性列表应该相对直接。

ASP 生成代码非常简单...

      var query = from column in info.Elements("table").Elements("tableProps").Elements("column")
                  select column;

      Panel ColumnPanel = new Panel();
      ColumnPanel.ScrollBars = ScrollBars.Vertical;
      ColumnPanel.Height = Unit.Pixel(400);

      this.Panel1.Controls.Add(new LiteralControl(@"<hr />"));
      this.Panel1.Controls.Add(ColumnPanel);

      foreach(XElement column in query)
      {
        // each column in its own 2 column div
        // Column name to the left, input field to the right.
        ColumnPanel.Controls.Add(new LiteralControl(@"<div class=""columnInfo"">"));

        // Get the name of the column and create a label using it.
        string columnName = column.AttributeString("Name", "!unnamed!");
        string columnComment = "";

        ColumnPanel.Controls.Add(new LiteralControl(@"<div class=""cellLeft"">"));
        ColumnPanel.Controls.Add(new LiteralControl(columnName));
        ColumnPanel.Controls.Add(new LiteralControl(@"</div>"));

        // Get the comment / description for the column.
        var qryText = from comment in column.Elements("columnProps")
                      select comment;

        // Despite the loop there'll only be one comment.
        foreach(var comment in qryText)
        {
          columnComment = comment.AttributeString("Comment", "");
        }

        // Create a multiline input text area with a default text given by
        // any existing comment attribute.
        ColumnPanel.Controls.Add(new LiteralControl(@"<div class=""cellRight"">"));
        TextBox description = new TextBox();
        description.ID = columnName;  
        description.Text = columnComment;
        description.Width = Unit.Pixel(500);
        description.Height = Unit.Pixel(40);
        description.TextMode = TextBoxMode.MultiLine;
        description.ToolTip = string.Format("Describe the purpose of [{0}] and any special rules that may apply to it.", columnName);
        description.MaxLength = (int)ExtendedProperty.SizeOf.Value;
        description.Attributes.Add("runat", "server");
        ColumnPanel.Controls.Add(description);
        ColumnPanel.Controls.Add(new LiteralControl("</div>"));

        this.Panel1.Controls.Add(new LiteralControl("</div>"));

      }

使用该实用工具

选择您的数据库、您感兴趣的表以及输出格式。

表名	表名或类似 %customer% 的 SQL 模式。留空以获取所选数据库中的所有表和视图。
格式	选择将用于生成输出的转换。除了 sp_help 之外，所有格式都会提示您下载输出。sp_help 格式会带您到一个如下所示的页面。
显示隐藏表	显示由实用程序标记为“隐藏”的表。当请求架构时，通常不会检索标记为隐藏的表的信息。当需要为第三方创建文档时，这对于不想包含敏感表详细信息的场景非常有用。

“sp_help”页面

更新表信息页面

示例 C# 输出

     using System.Reflection;

     namespace database.lbs
     {
     
      /// <summary>
      /// Column list for table.
      /// </summary>
      public class Address
      {
        /// <summary>
        /// Return the table's name. Address
        /// </summary>
        /// <returns />
        public static string _Self()
        {
          return "Address";
        }
        
        /// <summary>
        /// Return database size of named column.
        /// </summary>
        /// <returns />
        public static int _SizeOf(string columnName)
        {
          Address instance = new Address();
        
          FieldInfo f = instance.GetType().GetField(string.Format("siz_{0}", columnName)); 
          return Convert.ToInt32(f.GetValue(instance));
        }
        
        /// <summary>
        /// Return SQL database type of named column.    
        /// </summary>
        /// <returns />
        public static string _TypeOf(string columnName)
        {
          Address instance = new Address();
        
          FieldInfo f = instance.GetType().GetField(string.Format("typ_{0}", columnName)); 
          return Convert.ToInt32(f.GetValue(instance));
        }
        
        public const string ID = "ID";
        private const string typ_ID = "uniqueidentifier";
        private const int siz_ID = 16;
        public const string PostTown = "PostTown";
        private const string typ_PostTown = "varchar";
        private const int siz_PostTown = 20;
        public const string Postcode = "Postcode";
        private const string typ_Postcode = "varchar";
        private const int siz_Postcode = 15;
        public const string Building = "Building";
        private const string typ_Building = "varchar";
        private const int siz_Building = 30;
        public const string Block = "Block";
        private const string typ_Block = "varchar";
        private const int siz_Block = 150;
      }

     }

预期用途示例

    // Extracting data from ADO.Net data structures
    string postcode = myDataViewRow(Address.Postcode).ToString();

    // Restricting input length for string data.    
    txtPostcode.MaxLength = Address._Sizeof(Address.Postcode);

局限性和考虑因素

数据库访问

在小型组织中或对于我们这些在家玩弄的人来说，这不太可能成为问题。但在此处提供的实用程序假定数据库已配置为接受 SQL 和 Windows 登录。

扩展属性

表和列的扩展属性需要更新的每个表上的“alter table”权限。与上面的数据库访问一样，这在小型组织中不太可能成为问题，但在拥有严格控制的 DBA / 开发人员划分的大型组织中可能存在问题。

视图更改

我们必须牢记，随着 MS-SQL 的更新，Microsoft 可能会更改用于检索信息的任何视图的名称、结构和内容。

已知限制

• 该实用程序使用 Session 在页面之间移动数据。因此，请注意会话超时和其他与会话相关的漏洞。
• 用户输入没有任何验证。我们假设（我知道，我知道）这是供明智人士使用的。

关注点

HTML 输出中使用不间断空格以确保显示空的表列。在文档格式中可能不需要它们，但为了确保万无一失而保留了它们。

出于我尚未弄清楚的原因，Open Office Writer (3.2.0) 将带有 HTML doctype 标签的 HTML 文档视为纯文本，如果转换和下载输出的编码是 Unicode。将编码设置为 UTF-8 可以“解决”该问题。

结论

令人惊讶的是，即使是像这里描述的粗糙的实用程序也能如此有效。此工具的原始版本已在以下方面证明非常有效：

• 由于表列的用途和用法明确，不再需要从列名和表中保存的数据中推断，因此大大减少了掌握数据库不熟悉或不经常修改的部分的工作量。
• 通过使用自动生成的常量而不是字符串字面量来访问使用 ADO.Net 检索的数据，从而防止了运行时错误。
• 大大减少了创建设计和客户文档所需的时间。

如在此提供的，该实用程序适合小型团队使用，这些团队负责应用程序开发的所有方面，包括数据库设置和管理。但是，背景信息和示例代码应该为进一步开发提供一个良好的起点，即使它目前不合适。

历史

日期	备注
2005年12月	SQL-7 和 SQL-2000 的原始版本
2012年1月	重写。