将 SQL Server 2005 数据库图脚本化到文件

• 下载源代码 - 4.72 Kb

引言

Microsoft SQL Server 2000 具有一项功能，可让您生成架构图。这些图表以自定义二进制格式保存在 Enterprise Manager 的 [dtproperties] 表中——它们会与您的其他数据一起备份，但 *不易* 在数据库之间传输或添加到源代码管理（如 Visual SourceSafe）。

为了将图表移动到另一个数据库（或将其转换为“文本”格式以便进行源代码管理），您需要从 [dtproperties] 中提取二进制数据，并将其转换为更易于使用的格式——Clay Beatty 编写了一个针对 SQL Server 2000 的工具来执行此操作。

本文源代码 包含一个类似的工具，已重写为适用于 SQL Server 2005。它由一个存储过程和一个函数组成，它们协同工作以生成一组 INSERT/UPDATE 语句，您将这些语句应用于数据库以恢复源图表。

背景：SQL Server 2000 中的脚本图表

我最初是在 Google Groups 上看到了 Clay Beatty 的 usp_ScriptDatabaseDiagrams 存储过程，用于脚本图表，以及使其正常工作所需的另外两个对象：ufn_VarbinaryToVarcharHex 和 usp_dtpropertiesTextToRowset。

在过去的两年里，我一直在使用 usp_ScriptDatabaseDiagrams——因为我们的（“敏捷”）开发方法要求在持续集成过程中从脚本重建数据库，所以如果我们无法以某种方式持久化数据库图表，就会“丢失”它们。

能够脚本化图表意味着也可以在图表工具中直接进行一些“数据库文档记录”。这样完成的文档会方便地与数据库本身一起存储（因此其他开发人员在维护时很容易发现）。

SQL Server 2005

在我们的持续集成过程中，SQL Server 2005 的首批升级问题之一就是图表脚本失败……结果发现 SQL Server 2005 的一些更改破坏了 2000 年的代码。

• 图表数据现在存储在 [sysdiagrams] 中，而不是 [dtproperties] 中。
• [sysdiagrams] 每个图表只有一行，而 [dtproperties] 有七行。
• SQL Server 2005 引入了 VARBINARY(MAX) 数据类型。

SQL Server 2000 脚本中有大量代码用于管理 [dtproperties] 行（DtgSchemaOBJECT、DtgSchemaGUID、DtgSchemaNAME、DtgDSRefBYTES、DtgDSRefDATA、DtgSchemaBYTES、DtgSchemaDATA），这些行很可能存在 *因为* SQL Server 2000 对二进制数据的支持很笨拙。因此，这几乎是 600 行 Transact-SQL！

新的 [sysdiagrams] 表架构、VARBINARY(max) 的添加和 UPDATE 语句的增强，意味着旧脚本实际上不适用于 SQL 2005，必须从头重写。

关于新的 2005 代码

Tool_VarbinaryToVarcharHex	这个 **用户定义函数** 接收二进制数据作为参数，并输出其十六进制字符串表示。它是图表脚本过程的辅助函数。
Tool_ScriptDiagram2005	这个 **存储过程** 需要一个图表名称，它会从 [sysdiagrams] 中解析该名称。它会生成一组 INSERT 语句，在应用于具有相同架构的数据库时会重新创建图表。

工作原理

SQL Server 2005 的差异如此之大，以至于图表脚本被重写了——大约 120 行（带有很多 PRINT 语句）。远少于 SQL Server 2000 所需的 600 行！它执行三个主要步骤：

1) 获取源图表 ID 和二进制数据的大小

使用新的 SQL Server 2005 DATALENGTH 来确定我们需要处理多少数据（对步骤 #3 很重要）。我们还生成一个变量 @DiagramSuffix，用于确保图表名称唯一（未显示）。

SELECT 
    @diagram_id=diagram_id    
,    @size = DATALENGTH(definition) 
FROM sysdiagrams 
WHERE [name] = @name

2) 生成一个 INSERT 语句，该语句会在 [sysdiagrams] 中创建新行

此脚本基本上是从当前 [sysdiagrams] 行中选择值，并构建一个 INSERT 语句，通过 PRINT 命令输出。请注意，[definition] 列（包含图表的二进制数据）设置为 **Ox**——一个空的十六进制。这在 使用大型值数据类型 和 UPDATE.Write 时*很重要*，因为您*不能*对具有 NULL 值的列进行 .Write 操作。

SELECT @line =  
     'INSERT INTO sysdiagrams ([name], 
     [principal_id], [version], [definition])'
     + ' VALUES (''' + [name] + '''+@DiagramSuffix, '
     + CAST (principal_id AS VARCHAR(100)) + ', '
     + CAST (version AS VARCHAR(100))      + ', 0x)'
FROM sysdiagrams 
WHERE diagram_id = @diagram_id
PRINT @line

3) 创建 UPDATE 语句，抓取 [definition] 的块

在从 1 到 DATALENGTH([definition]) 的 WHILE 循环中，使用辅助函数 Tool_VarbinaryToVarcharHex 将二进制数据的块转换为可以 PRINT 输出并保存为文本的十六进制字符串。

WHILE @index < @size
BEGIN
    SELECT @line =  
     'UPDATE sysdiagrams SET [definition] .Write ('
    + ' 0x' + UPPER(dbo.Tool_VarbinaryToVarcharHex (
                    SUBSTRING (definition, @index, @chunk)))
    + ', null, 0) WHERE diagram_id = @newid '
    FROM    sysdiagrams 
    WHERE    diagram_id = @diagram_id

    PRINT @line
    SET @index = @index + @chunk
END

TRY/CATCH 错误处理

原始脚本中有大量的 IF @@ERROR <> 0 错误处理。相比之下，新的 2005 错误处理 要简单得多——语法对于 .NET 语言开发者来说很熟悉。

BEGIN TRY
    INSERT INTO sysdiagrams ([name], [principal_id], 
                             [version], [definition]) 
    VALUES ('All'+@DiagramSuffix, 1, 0, 0x)
    SET @newid = SCOPE_IDENTITY()
END TRY
BEGIN CATCH
    RETURN
END CATCH

这并不是 SQL 中 TRY/CATCH 用法的完美示例——没有 TRANSACTION 用法（所以没有显示 COMMIT/ROLLBACK），但您能理解意思。

要了解 Tool_VarbinaryToVarcharHex 的工作原理，请阅读 代码 中的注释——这在很大程度上（除添加了代码注释外）与 Clay 免费发布的 SQL Server 2000 版本相同。

运行“代码”

在数据库中执行这两个脚本后，使用它们生成图表的 INSERT/UPDATE 语句，如下所示：

exec Tool_ScriptDiagram2005 'All Tables'

这将创建一个看起来像这样的脚本（注意新的 SQL Server 2005 UPDATE.Write 命令）。

PRINT 'Create row for new diagram'
INSERT INTO sysdiagrams ([name], [principal_id], [version], [definition]) 
VALUES ('All'+@DiagramSuffix, 1, 0, 0x)
 
SET @newid = SCOPE_IDENTITY()
 
PRINT 'Write diagram id ' + CAST(@newid AS VARCHAR(100)) + ' into new row '
 
PRINT 'Now add all the binary data...'
UPDATE sysdiagrams 
    SET [definition] .Write ( 0xD0CF11E0A1B11AE1, null, 0) 
    WHERE diagram_id 
UPDATE sysdiagrams 
    SET [definition] .Write ( 0x0600000000000000, null, 0) 
    WHERE diagram_id 
-- ... Many more UPDATE rows

每个 UPDATE 会向 [sysdiagram] 行添加更多二进制数据，直到您可以将 UPDATE 保存到磁盘，将其添加到源代码管理，等等……当您想检索图表（到数据库的另一个副本/版本/备份）时，只需运行 UPDATE 脚本。输出将如下所示：

=== Tool_ScriptDiagram2005 restore diagram [all] ===
Suffix diagram name with date, to ensure uniqueness
Create row for new diagram
Write diagram all into new row (and get [diagram_id])
Now add all the binary data...
=== Finished writing diagram id 75  ===
=== Refresh your Databases-[DbName]-Database Diagrams 
    to see the new diagram ===</CODE>

在 SQL Server Management Studio 中右键单击 **数据库关系图** 文件夹，然后选择“刷新”。您的已恢复图表应已准备好查看！

结论

希望人们发现这个脚本与我发现 Clay Beatty 的 SQL Server 2000 原始脚本一样有用。

历史

• 2006-08-07：发布于 CodeProject。