HowTo Create, Export and Import SQL Server 2008 Database Diagrams

Documentation and communication of custom database models and objects should be a task with high importance in every project and for every good database developer and administrator! Personally i advise one of the following approaches for generating MSSQL Database documentation:

1. Reengineer DB-Diagrams with Visio 2010
(Needs Visio 2010 Professional! Only Professional Edition brings “DB-reengineer” feature! Visio 2007 brings SQL2000/2005 Support, and ONLY VISIO 2010 can read SQL2008 DBs. If you also think this is *** please vote at MS Connect for change)

OR/AND

2. Use SQL Server Management Studio (SSMS)  ootb functionality > Diagram Designer

 

This post ist all about the second (Diagram Designer) approach:

With SSMS its easy to create DB-diagrams:

 

With the integrated Diagram Designer you can produce multiple, nice looking diagrams, position tables and relationships as needed, create annotations…

Sample DB Diagram created with SSMS Diagram Designer

 

Limitations/Problems:

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:

Now you execute the stored procedure without parameters:

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:

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:

 

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; }