Unfortunately Diagram Designer misses features for printing or exporting your diagrams to formats like PDF. The only option possible is to make a screenshoot or use “ copy diagram to clipboard” function. Also its not possible to copy diagrams from one database to other databases or create diagrams automatically during deploment..!
Solution:
Diagrams are stored in database table [dbo].[sysdiagrams]. So if you would copy this table “by hand” to another database, the diagrams will be available in the destination DB.
To make this task easy you will find the code of a stored procedure at the end of this posting! This SP must be created in your custom database! After that you can call this SP to script-out existing diagrams as insert statement..Most of the honor goes to this blog-entry i found with google and adapted for my needs: http://www.conceptdevelopment.net/Database/ScriptDiagram2008/
Here’s the step-by-step howto:
You have already created my helper SP and have one or multiple diagrams created in your custom database:
http://jochenj.files.wordpress.com/2011/03/image2.png
Now you execute the stored procedure without parameters:
http://jochenj.files.wordpress.com/2011/03/image3.png
The output will create a exec statement for each diagram included in table dbo.sysdiagrams
If you execute the SP with @Name Parameter it will script-out the diagram to insert statements which can be executed in any database:
http://jochenj.files.wordpress.com/2011/03/image4.png
If you execute the generated insert statements in same database or if the diagram already exists in the database, a new/copy of the diagram will be created with timestamp in name:http://jochenj.files.wordpress.com/2011/03/image5.png
Finally here’s the Stored Procedure create statement. Just copy-paste and have fun!
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = ‘Tool_ScriptDiagram2008′)
BEGIN DROP PROCEDURE dbo.Tool_ScriptDiagram2008
END
GO
CREATE PROCEDURE [dbo].[Tool_ScriptDiagram2008]( @name VARCHAR(128) = NULL)
AS
BEGIN
DECLARE @diagram_id INT
DECLARE @index INT
DECLARE @size INT
DECLARE @chunk INT
DECLARE @line VARCHAR(max)
— Set start index, and chunk ‘constant’ value
SET @index = 1
SET @chunk = 32 — values that work: 2, 6 — values that fail: 15,16, 64
IF @name IS NULL
BEGIN
PRINT ‘WARNING! Stored Procedure was called without defined @Name parameter WARNING! ‘ + CHAR(10)+
‘Please call one of following TSQL to export a specific diagram’ +CHAR(10)
DECLARE @cnt int
DECLARE @cntMax int
SELECT @cnt=1;
SELECT @cntMax =Count(*) FROM dbo.sysdiagrams
DECLARE @y nvarchar(100)
WHILE @cnt <= @cntMax
BEGIN –While Loop
WITH AbfrageLoop AS (SELECT row_number() OVER (ORDER BY diagram_id) AS Row,* FROM dbo.sysdiagrams)
SELECT @y=name FROM AbfrageLoop WHERE Row=@cnt
–do something based on the query
print ‘EXEC [dbo].[Tool_ScriptDiagram2008] @Name = ‘“ + @y + ““
SELECT @cnt = @cnt+1
END –While Loop
RETURN (-1)
END
— Get PK diagram_id using the diagram‘s name (which is what the user is familiar with)
SELECT @diagram_id=diagram_id , @size = DATALENGTH(definition) FROM dbo.sysdiagrams WHERE [name] = @name
IF @diagram_id IS NULL
BEGIN
PRINT ‘/**<error>Diagram name [' + @name + '] could not be found.</error>*/‘
END
ELSE — Diagram exists
BEGIN — Now with the diagram_id, do all the work
PRINT ‘/**’
PRINT ‘<summary>Restore diagram “’ + @name + ““’</summary>’
PRINT ‘<generated>’ + LEFT(CONVERT(VARCHAR(23), GETDATE(), 121), 16) + ‘</generated>’
PRINT ‘*/‘
PRINT ‘PRINT “=== Tool_ScriptDiagram2008 restore diagram [' + @name + '] ===“‘
PRINT ‘ — If the sysdiagrams table has not been created in this database, create it!
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = “sysdiagrams“)
BEGIN
— Create table script generated by Sql Server Management Studio
— _Assume_ this is roughly equivalent to what Sql Server/Management Studio
— creates the first time you add a diagram to a 2008 database
CREATE TABLE [dbo].[sysdiagrams]( [name] [sysname] NOT NULL
,[principal_id] [int] NOT NULL
,[diagram_id] [int] IDENTITY(1,1) NOT NULL
,[version] [int] NULL
,[definition] [varbinary](max) NULL
,PRIMARY KEY CLUSTERED
([diagram_id] ASC)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF)
,CONSTRAINT [UK_principal_name] UNIQUE NONCLUSTERED
([principal_id] ASC,[name] ASC)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF)
)
EXEC sys.sp_addextendedproperty @name=N“microsoft_database_tools_support“, @value=1 , @level0type=N“SCHEMA“,@level0name=N“dbo“, @level1type=N“TABLE“,@level1name=N“sysdiagrams“
PRINT “[sysdiagrams] table was created as it did not already exist“
END
— Target table will now exist, if it didn“t before‘
PRINT ‘SET NOCOUNT ON — Hide (1 row affected) messages‘
PRINT ‘DECLARE @newid INT‘
PRINT ‘DECLARE @DiagramSuffix varchar (50)‘
PRINT ‘‘
PRINT ‘PRINT “Suffix diagram name with date, to ensure uniqueness“‘
PRINT ‘SET @DiagramSuffix = “ “ + LEFT(CONVERT(VARCHAR(23), GETDATE(), 121), 16)‘
PRINT ‘‘
PRINT ‘PRINT “Create row for new diagram“‘
— Output the INSERT that _creates_ the diagram record, with a non-NULL [definition],
— important because .WRITE *cannot* be called against a NULL value (in the WHILE loop)
— so we insert 0x so that .WRITE has ‘something‘ to append to…
PRINT ‘BEGIN TRY‘
PRINT ‘ PRINT “Write diagram ‘ + @name + ‘ into new row (and get [diagram_id])“‘
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 dbo.sysdiagrams WHERE diagram_id = @diagram_id
PRINT @line
PRINT ‘ SET @newid = SCOPE_IDENTITY()‘
PRINT ‘END TRY‘
PRINT ‘BEGIN CATCH‘
PRINT ‘ PRINT “XxXxX “ + Error_Message() + “ XxXxX“‘
PRINT ‘ PRINT “XxXxX END Tool_ScriptDiagram2008 – fix the error before running again XxXxX“‘
PRINT ‘ RETURN‘
PRINT ‘END CATCH‘
PRINT ‘‘
PRINT ‘PRINT “Now add all the binary data…“‘
PRINT ‘BEGIN TRY‘
WHILE @index < @size
BEGIN
— Output as many UPDATE statements as required to append all the diagram binary
— data, represented as hexadecimal strings
SELECT @line =
‘ UPDATE sysdiagrams SET [definition] .Write (‘ + ‘ ‘
+ UPPER(sys.fn_varbintohexstr (SUBSTRING (definition, @index, @chunk)))
+ ‘, null, 0) WHERE diagram_id = @newid — index:‘ + CAST(@index AS VARCHAR(100))
FROM sysdiagrams WHERE diagram_id = @diagram_id
PRINT @line
SET @index = @index + @chunk
END
PRINT ‘‘
PRINT ‘ PRINT “=== Finished writing diagram id “ + CAST(@newid AS VARCHAR(100)) + “ ===“‘
PRINT ‘ PRINT “=== Refresh your Databases-[DbName]-Database Diagrams to see the new diagram ===“‘
PRINT ‘END TRY‘
PRINT ‘BEGIN CATCH‘
PRINT ‘ — If we got here, the [definition] updates didn“t complete, so delete the diagram row‘
PRINT ‘ — (and hope it doesn“t fail!)‘
PRINT ‘ DELETE FROM sysdiagrams WHERE diagram_id = @newid‘
PRINT ‘ PRINT “XxXxX “ + Error_Message() + “ XxXxX“‘
PRINT ‘ PRINT “XxXxX END Tool_ScriptDiagram2008 – fix the error before running again XxXxX“‘
PRINT ‘ RETURN‘
PRINT ‘END CATCH’
END
END
GO
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, „Courier New“, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }