Click here to Skip to main content
15,034,598 members
Articles / Database Development / SQL Server
Posted 7 Aug 2006


95 bookmarked

Script SQL Server 2005 diagrams to a file

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


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

,    @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
    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

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:

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

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


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


  • 2006-08-07: posted on CodeProject.


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


About the Author

Web Developer
Australia Australia
-- ooo ---

Comments and Discussions

GeneralRe: Have no idea how to use this Pin
CloserWorlds28-Aug-08 19:59
MemberCloserWorlds28-Aug-08 19:59 
AnswerEven with all the source code available for you to read? Pin
craigd29-Aug-08 1:23
Membercraigd29-Aug-08 1:23 
GeneralRe: Even with all the source code available for you to read? Pin
stixoffire28-Apr-09 1:08
Memberstixoffire28-Apr-09 1:08 
Generalthanks!!! It works perfect!!! Pin
yeya8a8-Aug-08 7:07
Memberyeya8a8-Aug-08 7:07 
GeneralDoes not work Pin
Lord of Scripts27-Jul-08 22:44
MemberLord of Scripts27-Jul-08 22:44 
GeneralWorks for lots of others - more info? Pin
craigd28-Jul-08 0:17
Membercraigd28-Jul-08 0:17 
GeneralExcellent - Pin
gabe2622-Jul-08 22:04
Membergabe2622-Jul-08 22:04 
GeneralScripting multiple diagrams [modified] Pin
Voislav22-Apr-08 11:55
MemberVoislav22-Apr-08 11:55 

First of all thank you Craig.
I just prepared stored procedure for scripting multiple diagrams. The procedure uses your procedures.
If somebody needs it here it is:

<br />
BEGIN<br />
	DROP PROCEDURE dbo.Tool_ScriptDiagrams2005<br />
END<br />
GO<br />
create procedure dbo.Tool_ScriptDiagrams2005<br />
as<br />
begin<br />
	declare @CurrentPosition int;<br />
	declare @DiagramName nvarchar(128);<br />
	select @CurrentPosition = min(diagram_id) from dbo.sysdiagrams;<br />
	while (@CurrentPosition is not null) begin<br />
		select @DiagramName = name from dbo.sysdiagrams where diagram_id = @CurrentPosition;<br />
		exec Tool_ScriptDiagram2005 @DiagramName;<br />
		print 'GO'<br />
		select @CurrentPosition = min(diagram_id) from dbo.sysdiagrams where diagram_id > @CurrentPosition;<br />
	end<br />
end<br />
GO<br />


modified on Tuesday, April 22, 2008 6:01 PM

NewsNew version supporting multiple diagrams Pin
Joel Mansford1-Apr-08 12:13
MemberJoel Mansford1-Apr-08 12:13 
GeneralPerformance Pin
briancoll19-Feb-08 5:26
Memberbriancoll19-Feb-08 5:26 
GeneralRe: Performance Pin
craigd19-Feb-08 10:53
Membercraigd19-Feb-08 10:53 
GeneralStrange behaviour after running restore diagram [modified] Pin
bsjo24-Jan-08 22:39
Memberbsjo24-Jan-08 22:39 
GeneralRe: Strange behaviour after running restore diagram Pin
craigd31-Jan-08 12:14
Membercraigd31-Jan-08 12:14 
GeneralRe: Strange behaviour after running restore diagram Pin
bsjo3-Feb-08 3:56
Memberbsjo3-Feb-08 3:56 
GeneralWorks in SQL Server 2008 Pin
craigd11-Dec-07 9:13
Membercraigd11-Dec-07 9:13 
junkmail@expedicity.com2-Oct-07 9:01
Memberjunkmail@expedicity.com2-Oct-07 9:01 
NewsSQL Server 2008 (katmai) update Pin
craigd4-Sep-07 12:55
Membercraigd4-Sep-07 12:55 
GeneralVery Handy Pin
ThomasTaylor7-Aug-07 15:09
MemberThomasTaylor7-Aug-07 15:09 
GeneralRe: Very Handy Pin
vmp_pdx20-Mar-08 8:44
Membervmp_pdx20-Mar-08 8:44 
Generalthanks a lot Pin
I.Saravanan22-Jun-07 5:22
MemberI.Saravanan22-Jun-07 5:22 
GeneralThank you Craig! (Clay Beatty here) Pin
cabeatty2-May-07 3:31
Membercabeatty2-May-07 3:31 
GeneralNo problem, thank _you_ Pin
craigd2-May-07 11:43
Membercraigd2-May-07 11:43 
GeneralRe: No problem, thank _you_ Pin
brian86553-Feb-08 8:25
Memberbrian86553-Feb-08 8:25 
GeneralGreat Work! Small Typo found Pin
Rainer Halanek16-Mar-07 6:52
MemberRainer Halanek16-Mar-07 6:52 
GeneralRe: Great Work! Small Typo found Pin
tartheod6-Nov-07 7:50
Membertartheod6-Nov-07 7:50 

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.