Startseite > Allgemein > HowTo Create, Export and Import SQL Server 2008 Database Diagrams

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

 

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

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

image

Now you execute the stored procedure without parameters:

image

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:

image

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

 

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=Nmicrosoft_database_tools_support, @value=1 , @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=Nsysdiagrams                  
                    
       PRINT [sysdiagrams] table was created as it did not already exist             
       END              
       — Target table will now exist, if it didnt 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 didnt complete, so delete the diagram row‚       
         PRINT ‚
    — (and hope it doesnt 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; }

Kategorien:Allgemein Schlagwörter: , , , , ,
  1. 6. Dezember 2011 um 05:50

    Though you can not export to pdf or any other format directly off Sql Server 2008 R2, still you can copy and paste the diagram in a zoomed in position from Sql Server Management Studio to MS Visio. This way you can create a decent drawing which can be subsequently exported to pdf and related formats, Visio supports.

    • 8. Dezember 2011 um 22:25

      nice hint! but i couldn’t find any advantages pasting this in visio, it’s still just a screenshoot you could also paste into word,onenonte…or any other tool which can generate pdf! Or do i miss something?

  2. 8. Dezember 2011 um 22:27

    this is the most read article in my hole blog😉 so i thought maybe for the readers also this forum entry is relevant:

    Database Reverse Engineering – Howto display non-table but code objects (procs,functions) as shape?

    http://social.msdn.microsoft.com/Forums/en-US/visiogeneral/thread/fb03cd4b-73f3-4fa2-b8e6-a20860ba70a3

  3. Cathy
    14. Januar 2014 um 17:41

    Thank you for your post! This was very helpful to me.

  4. candide
    19. Mai 2015 um 17:28

    Thanks for your code, it was possible for me to downgrade a diagram from SS2012 to SS2008R2!

  1. 19. August 2011 um 19:40
  2. 1. Januar 2014 um 15:09
  3. 7. Oktober 2014 um 04:07

Schreibe einen Kommentar

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s

%d Bloggern gefällt das: