Deploy database diagram changes along with table changes

I’ve often wished, when I did a database compare, that changes to sysdiagrams would be detected. That they could be incorporated into the project and that deploy could put them on the target system.  Upon reading someone else complain about the same thing it suddenly occurred to me that we could come very close to achieving this (in some cases actually achieving it) with very little effort.

Step 1:  Create a procedure that scripts a diagram to a form visible to database compare.
Step 2:  Put a trigger on sysdiagrams to run this procedure each time a diagram is updated.
Step 3:  Add a Post-Deploy script to your deployment that contains commands to execute the scripts that update the diagram.

Although most of the process is straightforward there are a couple of gotchas.  The first one is how to store the script so that compare will notice it has changed.  This was rather easy.  I script the diagram to a custom procedure named ‘UpsertDiagram_’+<diagram name>.  This results in my having a procedure for every diagram.  The trigger automatically creates/updates the procedure.  The procedure change is detected by Schema Compare and the deployment can then be automatic.  As a side effect the diagram is version controlled along with the rest of the project (because everybody version controls all their projects!).

The next gotcha is that unless you have a post-deploy that executes the procedures, the diagrams don’t actually get updated.  There isn’t much that can be done about this other than to execute the procedures manually but at least they exist and are up-to-date. I suppose a DDL trigger to execute ‘UpsertDiagram_XXX’ procedures if they are updated could work but I’ve not tried it.

The last point is that you could create a conflict if you run ‘UpsertDiagram_XXX’ procedures on a system with a trigger that updates the procedure when the diagram is updated.  A simple trick with a little known function can allow your trigger to detect this condition and prevent conflict.  The key is to use CONTEXT_INFO(). By looking for a specific value that will only exist while our custom procedure runs you can skip execution of the update procedure.

This first procedure will add a suitable trigger. The second scripts the diagram into a custom procedure.


CREATE PROCEDURE AutomaticallyUpdateDiagramProcedures AS
DECLARE @CMD VARCHAR(4000) = ' trigger iut_sysdiagrams on sysdiagrams for update,insert as
    if OBJECT_ID(''UpsertDiagramProc'') is not null and (CONTEXT_INFO() IS NULL OR CONTEXT_INFO() != CAST(''UpsertDiagram'' AS varbinary(128)))
        declare @CMD varchar(max) = '''';

        select @CMD += CHAR(10)+''EXEC UpsertDiagramProc ''''''+name+'''''';''
          from inserted;

IF OBJECT_ID('dbo.iut_sysdiagrams') is null 
    SET @CMD = 'ALTER'+@CMD;

IF OBJECT_ID('dbo.sysdiagrams') is not null


-- Creates a stored procedure for the named database diagram that can recreate the diagram.
-- This allows a database project to track diagram changes as well as deploy them.
-- Although deployment only deploys procedures, the created proc can be run in a post-deploy script to effect updates.
-- Note that diagrams only reference objects. They don't contain definitions for objects.  
-- So objects must exist on the new system to appear in the diagram. 
-- 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 add support for principal_id using the function DATABASE_PRINCIPAL_ID('dbo'). 
-- This tool just copies the existing ID which is usually OK. 
CREATE PROCEDURE dbo.UpsertDiagramProc (@name VARCHAR(128),@Debug bit = 0)
  DECLARE @Chunk        INT = 50;
  DECLARE @Index        INT = 1;
  DECLARE @Block        INT;
  DECLARE @Diagram_ID   INT;
  DECLARE @PrincipalID  VARCHAR(10);
  DECLARE @Version      VARCHAR(10);
  DECLARE @Size         INT;
  DECLARE @Line         VARCHAR(MAX);
   -- Get PK diagram_id using the diagram's name. 
  SELECT  @Diagram_ID  = diagram_id 
         ,@Size         = DATALENGTH(definition) 
         ,@PrincipalID  = principal_id
         ,@Version      = [version]
  FROM    sysdiagrams 
  WHERE   [name] = @name;
  IF @Diagram_ID IS NULL  
      PRINT '/**';
      PRINT 'Diagram name [' + @name + '] could not be found.';
      PRINT '*/';
  ELSE -- Diagram exists 
      SET @ProcDef  ='IF OBJECT_ID(''UpsertDiagram_'+@name+''') IS NULL EXEC(''CREATE PROCEDURE UpsertDiagram_'+@name+' AS RETURN NULL;'');';
      IF @Debug = 1
        SELECT @ProcDef;
      SET @ProcDef='ALTER PROCEDURE UpsertDiagram_'+@name+' AS'
        + CHAR(10)+'BEGIN' 
        + CHAR(10)+'  /**' 
        + CHAR(10)+'      Restores diagram ''' + @name + '''' 
        + CHAR(10)+'    ' 
        + CHAR(10)+'      Generated with: UpsertDiagramProc ''' + @name + '''' 
        + CHAR(10)+'      Creates the [sysdiagrams] table if it doesn''t exist.' 
        + CHAR(10)+'      Restores the named diagram as it existed when this proc was created.' 
        + CHAR(10)+'    ' 
        + CHAR(10)+'    ' + LEFT(CONVERT(VARCHAR(23), GETDATE(), 121), 16) + '' 
        + CHAR(10)+'     Author: Rick Bielawski' 
        + CHAR(10)+'  **/' 
        + CHAR(10)
        --            The context value must be synced with the trigger code that automatically updates this procedure upon diagram change.
        --            We don't want this procedure updated while it puts the very diagram we created it to update into the table
        + CHAR(10)+'  DECLARE @OldContext varbinary(128) = ISNULL(CONTEXT_INFO(),0X)'
        + CHAR(10)+'         ,@NewContext varbinary(128) = CAST(''UpsertDiagram'' AS varbinary(128));' 
        + CHAR(10)+'  SET CONTEXT_INFO @NewContext;'
        + CHAR(10)+'  PRINT ''=== Start creation/update of diagram in ' + DB_NAME() + ' ==='';' 
        + CHAR(10)
        + CHAR(10)+'  -- If the sysdiagrams table has not been created in this database, create it! ' 
        + CHAR(10)+'    BEGIN ' 
        + CHAR(10)+'      -- Create table script generated by Sql Server Management Studio ' 
        + CHAR(10)+'      -- _Assume_ this is roughly equivalent to what Sql Server/Management Studio ' 
        + CHAR(10)+'      -- creates the first time you add a diagram to a 2008 database ' 
        + CHAR(10)+'      CREATE TABLE [dbo].[sysdiagrams]( ' 
        + CHAR(10)+'            [name] [sysname] NOT NULL, ' 
        + CHAR(10)+'            [principal_id] [int] NOT NULL, ' 
        + CHAR(10)+'            [diagram_id] [int] IDENTITY(1,1) NOT NULL, ' 
        + CHAR(10)+'            [version] [int] NULL, ' 
        + CHAR(10)+'            [definition] [varbinary](max) NULL, ' 
        + CHAR(10)+'      PRIMARY KEY CLUSTERED  ' 
        + CHAR(10)+'        ( ' 
        + CHAR(10)+'            [diagram_id] ASC ' 
        + CHAR(10)+'        )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) , ' 
        + CHAR(10)+'      CONSTRAINT [UK_principal_name] UNIQUE NONCLUSTERED  ' 
        + CHAR(10)+'        ( ' 
        + CHAR(10)+'            [principal_id] ASC, ' 
        + CHAR(10)+'            [name] ASC ' 
        + CHAR(10)+'        )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF)  ' 
        + CHAR(10)+'        );  ' 
        + CHAR(10)+'      EXEC sys.sp_addextendedproperty @name=N''microsoft_database_tools_support'', ' 
        + CHAR(10)+'           @value=1 , @level0type=N''SCHEMA'',@level0name=N''dbo'', ' 
        + CHAR(10)+'           @level1type=N''TABLE'',@level1name=N''sysdiagrams''; ' 
        + CHAR(10)+'      PRINT ''[sysdiagrams] table required creation'';' 
        + CHAR(10)+'    END ' 
        + CHAR(10)+'  -- Target table will now exist, if it didn''t before' 
        + CHAR(10)+'  SET NOCOUNT ON -- Hide (1 row affected) messages' 
        + CHAR(10)+'  DECLARE @newid INT;' 
        + CHAR(10)+'  SELECT @newid = [diagram_id] from sysdiagrams where [name] = ''' + @name + ''';' 
        + CHAR(10)    
        + CHAR(10)+'  BEGIN TRY' 
        + CHAR(10)+'    IF @newid IS NULL' 
        + CHAR(10)+'    BEGIN' 
        + CHAR(10)+'        Print ''Inserting new ''''' + @name + ''''' diagram'';'
        + CHAR(10)+'        INSERT INTO sysdiagrams ([name], [principal_id], [version], [definition])' 
        + CHAR(10)+'        VALUES (''' + @name + ''', ' + @PrincipalID  + ', ' + @Version + ', 0x);' 
        + CHAR(10)+'        SET @newid = SCOPE_IDENTITY();' 
        + CHAR(10)+'    END' 
        + CHAR(10)+'    ELSE' 
        + CHAR(10)+'    BEGIN' 
        + CHAR(10)+'        PRINT ''Updating existing ''''' + @name + ''''' diagram'';' 
        + CHAR(10)+'        UPDATE sysdiagrams ' 
        + CHAR(10)+'           SET [principal_id] = ' + @PrincipalID 
        + CHAR(10)+'              ,[version]      = ' + @Version 
        + CHAR(10)+'              ,[definition]   = 0x'
        + CHAR(10)+'        WHERE diagram_id = @newid;' 
        + CHAR(10)+'    END';
      -- Output diagram data encoded as hex strings in a way that tries to be readable and comparable.
      WHILE @Index < @Size  
          SET @Block = 1;
          SET @ProcDef += CHAR(10)+CHAR(10)+'    UPDATE sysdiagrams SET [definition] .Write ( 0X' 
          -- The operation must be done in blocks because there is no way to represent binary literal values greater than 8000 bytes
          WHILE @Block < 8001-@Chunk AND @Index < @Size  
              SELECT  @ProcDef += CHAR(10)+'     + ' 
                               + UPPER(sys.fn_varbintohexstr(SUBSTRING(definition, @Index, @Chunk))) + ' -- index:' + CAST(@Index AS VARCHAR) 
              FROM    sysdiagrams 
              WHERE   diagram_id = @diagram_id;
              SET @Block += @Chunk;
              SET @Index += @Chunk;
          SET @ProcDef += CHAR(10)+'     , null, 0) WHERE diagram_id = @newid;';
      SET @ProcDef 
        + CHAR(10)+'    PRINT ''=== Finished writing diagram id '' + CAST(@newid AS VARCHAR) + '' to ' + DB_NAME() + ' ==='';' 
        + CHAR(10)+'  END TRY' 
        + CHAR(10)+'  BEGIN CATCH' 
        + CHAR(10)+'    -- If we got here, the [definition] updates didn''t complete, so delete the diagram row' 
        + CHAR(10)+'    -- (and hope it doesn''t fail!)' 
        + CHAR(10)+'    DELETE FROM sysdiagrams WHERE diagram_id = @newid;' 
        + CHAR(10)+'    PRINT ''***** '' + Error_Message() + '' *****'';' 
        + CHAR(10)+'    PRINT ''***** END ScriptDiagram - fix the error before running again *****'';' 
        + CHAR(10)+'    RETURN' 
        + CHAR(10)+'  END CATCH' 
        + CHAR(10)+'  SET CONTEXT_INFO @OldContext;'
        + CHAR(10)+'END' 

      IF @Debug = 1
        SELECT CAST('<?M '+@ProcDef+'?'+'>' AS xml) AS ProcDef;

      PRINT 'UpsertDiagram_'+@name+' PROCEDURE Upserted.';

Leave a Reply

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

You are commenting using your 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: