在数据库开发和管理过程中,经常需要将数据库图从一个数据库迁移到另一个数据库,或者将其添加到源代码控制中。SQL Server 2005提供了一个功能强大的数据库图工具,但是这些图是以二进制格式存储在[sysdiagrams]
表中的,这给迁移和版本控制带来了一定的困难。本文将介绍一种方法,通过自定义存储过程和函数,将数据库图以文本格式输出,方便迁移和版本控制。
在SQL Server2000中,Clay Beatty开发了一个名为usp_ScriptDatabaseDiagrams
的存储过程,用于将数据库图脚本化。但是,随着SQL Server 2005的发布,原有的脚本不再适用,因为2005版本在存储数据库图的方式上做了一些改变。
在SQL Server2005中,数据库图的数据存储在[sysdiagrams]
表中,而不是SQL Server 2000中的[dtproperties]
表。此外,[sysdiagrams]
表中每个图只有一行数据,而[dtproperties]
表中有七行。SQL Server 2005还引入了VARBINARY(MAX)
数据类型,这为处理大型二进制数据提供了便利。
为了解决SQL Server 2005中数据库图迁移的问题,编写了一个新的工具,包括一个存储过程Tool_ScriptDiagram2005
和一个用户定义的函数Tool_VarbinaryToVarcharHex
。这个工具可以将数据库图中的二进制数据转换为十六进制字符串,然后生成一系列INSERT
和UPDATE
语句,用于在另一个数据库中重建数据库图。
这个用户定义的函数接受二进制数据作为参数,并输出其十六进制字符串表示。它是脚本化数据库图过程中的一个辅助函数。
这个存储过程需要一个数据库图的名称作为参数,然后从[sysdiagrams]
表中解析这个名称。它生成一系列INSERT
语句,当应用到具有相同架构的数据库时,可以重新创建数据库图。
由于SQL Server 2005的变化,数据库图脚本被从头开始重写,大约120行代码(包含大量的PRINT
语句)。这比SQL Server 2000中需要的600行Transact-SQL代码要少得多。它主要执行以下三个步骤:
使用新的SQL Server2005DATALENGTH
函数来确定需要处理的数据量(这对于步骤3很重要)。还生成了一个变量@DiagramSuffix
,用于确保数据库图名称的唯一性。
SELECT @diagram_id=diagram_id, @size=DATALENGTH(definition) FROM sysdiagrams WHERE [name]=@name
这个脚本基本上是从当前的[sysdiagrams]
行中选择值,并构建一个INSERT
语句,通过PRINT
命令输出。注意[definition]
列(包含数据库图的二进制数据)被设置为0x
- 一个空的十六进制。这在使用大型值数据类型和UPDATE.Write
时很重要,因为不能向一个NULL
值的列写入。
SELECT @line='INSERT INTO sysdiagrams ([name], [principal_id], [version], [definition]) VALUES ('''+ [name] +''','+ CAST (principal_id AS VARCHAR(100)) +', '+ CAST (version AS VARCHAR(100)) +', 0x)' FROM sysdiagrams WHERE diagram_id=@diagram_id
PRINT @line
在一个从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
原始脚本中有很多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
在数据库中执行这两个脚本后,使用它们为数据库图生成INSERT
/UPDATE
语句,如下所示:
EXEC Tool_ScriptDiagram2005 'All Tables'
这将创建一个脚本,如下所示(注意新的SQL Server 2005UPDATE.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