Script SQL Server 2005 diagrams to a file

• Download source code - 4.72 Kb

Introduction

Microsoft SQL Server 2000 has a feature that allows you to generate diagrams of your schema. These diagrams are saved in a custom, binary format by the Enterprise Manager in the [dtproperties] table - they are backed-up with the rest of your data, but they are not easy to transfer between databases or add to source control (like Visual SourceSafe).

In order to move a diagram to another database (or get it into a 'text' format for source control), you need to extract the binary data from [dtproperties] and get it into a more usable format - Clay Beatty wrote a tool to do just that for SQL Server 2000.

The code for this article contains a similar tool, re-written for SQL Server 2005. It consists of a stored procedure and function that work together to generate a set of INSERT/UPDATE statements which you apply to a database in order to restore the source diagram.

Background: Scripting diagrams in SQL Server 2000

I originally came across Clay Beatty's usp_ScriptDatabaseDiagrams stored procedure to script diagrams on Google groups, along with the two other objects required for it to work: ufn_VarbinaryToVarcharHex and usp_dtpropertiesTextToRowset.

I've been using usp_ScriptDatabaseDiagrams for the past two years - since our ("agile") development methodology calls for the database to be rebuilt from script as part of the continuous integration process, we would 'lose' database diagrams if we couldn't persist them in some way.

Being able to script the diagrams means it also makes sense to do some 'database documentation' directly in the diagram tool. Documentation done this way is conveniently stored with the database itself (so it's easy for other developers to discover during maintenance).

SQL Server 2005

One of the first upgrading issues with SQL Server 2005 in our continuous integration process was the failure of the diagram scripts... Turns out there are a number of changes in SQL Server 2005 which break the code from 2000:

• The diagram data is now stored in [sysdiagrams] and not [dtproperties]
• [sysdiagrams] has only one row per diagram, whereas [dtproperties] has seven rows
• SQL Server 2005 introduces the VARBINARY(MAX) data type

There is a lot of code in the SQL Server 2000 script to manage the [dtproperties] rows (DtgSchemaOBJECT, DtgSchemaGUID, DtgSchemaNAME, DtgDSRefBYTES, DtgDSRefDATA, DtgSchemaBYTES, DtgSchemaDATA), which in turn are probably there because of the clumsy support for binary data in SQL Server 2000. For this reason, it's almost 600 lines of Transact-SQL!

The new [sysdiagrams] table schema, addition of VARBINARY(max) and UPDATE statement enhancements, meant that the old script was not really applicable to SQL 2005, and had to be re-written from scratch.

About the new 2005 code

How it works

The differences in SQL Server 2005 are so great that the diagram script was re-written from scratch - in around 120 lines (with lots of PRINT statements). Much less than the 600 lines required in SQL Server 2000! It performs three main steps:

1) Get the source diagram ID and the size of the binary data

Uses the new SQL Server 2005 DATALENGTH to determine how much data we need to process (important for step #3). We also generate a variable @DiagramSuffix which is used to ensure diagram names are unique (not shown).

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

2) Generate an INSERT statement that creates a new row in [sysdiagrams]

This script is basically selecting the values from the current [sysdiagrams] row, and building an INSERT statement which is output via the PRINT command. Notice the [definition] column (which contains the binary data for the diagram) is set to Ox - an empty hexadecimal. This is important when Using Large-Value Data Types and UPDATE.Write because you cannot .Write to a column with a NULL value.

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) Create UPDATE statements grabbing chunks of [definition]

In a WHILE loop from 1 to DATALENGTH([definition]), use the helper function Tool_VarbinaryToVarcharHex to convert chunks of binary data into hexadecimal strings that can be PRINTed out and saved as text.

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 error handling

There's a great deal of IF @@ERROR <> 0 error handling in the original script. By contrast, the new 2005 error handling is a lot simpler - the syntax will be familiar to .NET language developers:

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

This isn't exactly the perfect example of TRY/CATCH usage in SQL - no TRANSACTION usage (so it doesn't show COMMIT/ROLLBACK) - but you get the idea.

To see how Tool_VarbinaryToVarcharHex works, read through the comments in the code - this is predominantly unchanged (except for the addition of code comments) from the SQL Server 2000 version which was freely posted by Clay.

Running the 'code'

Once you've executed the two scripts in your database, use them to generate INSERT/UPDATE statements for your diagrams like this:

exec Tool_ScriptDiagram2005 'All Tables'

Which will create a script that looks like this (notice the new SQL Server 2005 UPDATE.Write command):

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

Each UPDATE adds a little more binary data to the [sysdiagram] row until you can save the UPDATEs to disk, add them to source control, whatever... When you wish to retrieve the diagram (to another copy/version/backup of the database), just run the UPDATE script. The output will look like this:

=== 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>

Right-click the Database Diagrams folder in the SQL Server Management Studio, and choose 'Refresh'. Your restored diagram should be ready to view!

Conclusion

Hopefully, people find this script as useful as I found Clay Beatty's original for SQL Server 2000.

History

• 2006-08-07: posted on CodeProject.