Scripting an SSMS diagram

Yes, diagrams can be moved from system to system.  Any objects referenced by a diagram that do not exist where ‘restored’ will simply be deleted from the diagram.  Since diagrams might be created, saved and closed – then the underlying objects deleted or changed before the diagram is re-opened the rendering engine must be quite robust and so restoring a diagram can’t cause any issues to the underlying database.

IF OBJECT_ID('ScriptDiagram') IS NOT NULL 
  DROP PROCEDURE dbo.ScriptDiagram;
GO
-- Script a database diagram. 
-- Typically to copy it to another database that has exactly the same objects.
-- Note that diagrams only reference objects.
-- They don't contain definitions for objects so the objects must exist.
-- The references are by name not ID so the behavior most people want usually happens.
-- The required parameter is the name of the diagram sans the principal_id. 
-- You can support principal_id using the function DATABASE_PRINCIPAL_ID('dbo').
-- This tool just copies the existing ID which is usually OK.
CREATE PROCEDURE [dbo].[ScriptDiagram] (@name VARCHAR(128))
AS 
BEGIN
  DECLARE @diagram_id INT
  DECLARE @size INT
  DECLARE @chunk INT = 32
  DECLARE @index INT = 1
  DECLARE @line VARCHAR(MAX)
   
   -- Get PK diagram_id using the diagram's name.
  SELECT  @diagram_id = diagram_id
         ,@size = DATALENGTH(definition)
  FROM    sysdiagrams
  WHERE   [name] = @name

  IF @diagram_id IS NULL 
    BEGIN
      PRINT '/**<error>'
      PRINT 'Diagram name [' + @name + '] could not be found.'
      PRINT '</error>*/' 
    END
  ELSE -- Diagram exists
    BEGIN
         -- Use the diagram_id (not the name) to retrieve data for the script
      PRINT 'USE ' + DB_NAME()
      PRINT '/**'
      PRINT '<summary>'
      PRINT 'Restore diagram ''' + @name + ''''
      PRINT '</summary>'
      PRINT '<remarks>'
      PRINT 'Generated by ScriptDiagram'
      PRINT 'Will attempt to create [sysdiagrams] table if it doesn''t already exist'
      PRINT '</remarks>'
      PRINT '<generated>' + LEFT(CONVERT(VARCHAR(23), GETDATE(), 121), 16) + '</generated>'
      PRINT '*/'
      PRINT '    PRINT ''=== Start creation/update of diagram in ' + DB_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 required creation''
          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 'SELECT @newid = [diagram_id] from sysdiagrams where [name] = ''' + @name + ''';'
      PRINT ''
         -- For simplicity, the record should look the same whether we're updating or creating new
      PRINT 'BEGIN TRY'
      PRINT '    IF @newid IS NULL'
      PRINT '    BEGIN'
      PRINT '        Print ''Inserting new ''''' + @name + ''''' diagram'''
      SELECT  @line = '        INSERT INTO sysdiagrams ([name], [principal_id], [version], [definition])'
              + ' VALUES (''' + [name] + ''', ' + CAST (principal_id AS VARCHAR(100)) + ', '
              + CAST (version AS VARCHAR(100)) + ', 0x)'
      FROM    sysdiagrams
      WHERE   diagram_id = @diagram_id
      PRINT @line
      PRINT '        SET @newid = SCOPE_IDENTITY()'
      PRINT '    END'
      PRINT '    ELSE'
      PRINT '    BEGIN'
      PRINT '        PRINT ''Updating existing ''''' + @name + ''''' diagram'''
      SELECT  @line = '        UPDATE sysdiagrams SET [name] = ''' + [name] + ''', ' + '[principal_id] = '
              + CAST (principal_id AS VARCHAR(100)) + ', ' + '[version] = ' + CAST (version AS VARCHAR(100)) + ' '
              + 'WHERE diagram_id = @newid'
      FROM    sysdiagrams
      WHERE   diagram_id = @diagram_id
      PRINT @line
      PRINT '        UPDATE sysdiagrams SET [definition] .Write(null, 0, 0) WHERE diagram_id = @newid'
      PRINT '    END'
      PRINT ''
         -- Now, one way or another the record is ready to have binary data appended.
         -- Output UPDATE statements to append diagram data encoded as hex strings.
      WHILE @index < @size 
        BEGIN
          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)) + '' to ' + DB_NAME()
        + ' ==='''
      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 ''***** '' + Error_Message() + '' *****'''
      PRINT '    PRINT ''***** END ScriptDiagram - fix the error before running again *****'''
      PRINT '    RETURN'
      PRINT 'END CATCH'
    END
END
/*
GO
ScriptDiagram 'TestDiagram';
--*/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: