Click here to Skip to main content
15,867,488 members
Articles / Database Development / SQL Server
Article

Script SQL Server 2005 diagrams to a file

Rate me:
Please Sign up or sign in to vote.
4.94/5 (55 votes)
7 Aug 2006CPOL5 min read 333.9K   5.1K   95   75
Save your database diagrams *outside* the database in a Transact-SQL text file, for backup and/or source-control.

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

Tool_VarbinaryToVarcharHexThis User Defined Function takes some binary data as a parameter, and outputs a hexadecimal string representation. It is a helper function for the diagram scripting procedure.
Tool_ScriptDiagram2005This Stored Procedure requires a diagram name, which it parses from [sysdiagrams]. It creates a set of INSERT statements that re-create the diagram when applied to a database with the same schema.

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).

SQL
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.

SQL
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.

SQL
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:

SQL
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):

SQL
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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Web Developer
Australia Australia
-- ooo ---
www.conceptdevelopment.net
conceptdev.blogspot.com
www.searcharoo.net
www.recipenow.net
www.racereplay.net
www.silverlightearth.com

Comments and Discussions

 
QuestionThanks Pin
Member 1081286317-Nov-14 4:30
Member 1081286317-Nov-14 4:30 
QuestionPublished on github - get in touch Pin
Tim Abell22-Oct-14 22:19
Tim Abell22-Oct-14 22:19 
GeneralSearch through the binary for all contained texts (i.e. table names) Pin
Bitsqueezer1-Oct-14 5:33
Bitsqueezer1-Oct-14 5:33 
QuestionNice :D Pin
Gilberto-TRD22-May-13 6:35
Gilberto-TRD22-May-13 6:35 
QuestionThanks a lot! Pin
Robsta21-Jun-11 1:24
professionalRobsta21-Jun-11 1:24 
GeneralMy vote of 5 Pin
Member 67312830-Jun-10 21:54
Member 67312830-Jun-10 21:54 
GeneralPerfect!!! Pin
canercaner28-Jan-10 0:18
canercaner28-Jan-10 0:18 
GeneralThanks a lot Pin
jesteban197929-Oct-09 10:12
jesteban197929-Oct-09 10:12 
GeneralThank you! Pin
PhantomPalmer29-Oct-09 5:08
PhantomPalmer29-Oct-09 5:08 
QuestionWhat about SQL 2008? Pin
Wes Jones14-Oct-09 6:01
Wes Jones14-Oct-09 6:01 
AnswerRe: What about SQL 2008? Pin
craigd16-Oct-09 22:21
craigd16-Oct-09 22:21 
That script should still work in SQL Server 2008 (from memory), however due to additional features in 2008 there is a new version that is much simpler.

The SQL Server 2008 version can be downloaded here:

http://www.conceptdevelopment.net/Database/ScriptDiagram2008/[^]

HTH
GeneralCan't believe this still isn't a supported feature Pin
Doug_Bell20-Aug-09 7:00
Doug_Bell20-Aug-09 7:00 
GeneralExcellent! Pin
Jörgen Sigvardsson13-Jul-09 22:01
Jörgen Sigvardsson13-Jul-09 22:01 
GeneralThank you - just what I was looking for Pin
Adam Edell11-Jun-09 10:21
Adam Edell11-Jun-09 10:21 
GeneralRe: Thank you - just what I was looking for Pin
craigd11-Jun-09 23:49
craigd11-Jun-09 23:49 
QuestionError when opening created diagram: "Table(s) were removed..." Pin
Doctor Mist13-May-09 10:43
Doctor Mist13-May-09 10:43 
AnswerRe: Error when opening created diagram: "Table(s) were removed..." Pin
craigd19-May-09 13:49
craigd19-May-09 13:49 
GeneralRe: Error when opening created diagram: "Table(s) were removed..." Pin
Doctor Mist20-May-09 7:57
Doctor Mist20-May-09 7:57 
GeneralRe: Error when opening created diagram: "Table(s) were removed..." Pin
craigd20-May-09 12:58
craigd20-May-09 12:58 
GeneralTool_ScriptDiagram2005 Pin
ydancy25-Mar-09 8:55
ydancy25-Mar-09 8:55 
GeneralWhat version? Verify sysdiagrams table schema... Pin
craigd25-Mar-09 14:12
craigd25-Mar-09 14:12 
GeneralRe: What version? Verify sysdiagrams table schema... Pin
ydancy26-Mar-09 6:10
ydancy26-Mar-09 6:10 
GeneralRe: What version? Verify sysdiagrams table schema... Pin
craigd26-Mar-09 16:09
craigd26-Mar-09 16:09 
QuestionSource code can't be downloaded! Pin
tempsh24-Jan-09 20:35
tempsh24-Jan-09 20:35 
AnswerWorks for me - also alternate download location Pin
craigd24-Jan-09 21:39
craigd24-Jan-09 21:39 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.