How to install database diagram support programmatically

In the previous article we discussed about how migrating a database diagram into another database. In this article I’ll quickly show you how to install database diagram support. As mentioned in the” How to copy or migrate database diagrams into another database” article, we need to install database diagram support to be able to see the migrated database diagrams. We also explained a very easy way to install database diagram support from SSMS in the “How to store a SQL Server database diagram into a file and share it with others?” article. Now, assume that we want to migrate the database diagrams into several SQL Server instances. It seems that it might be better if we can implement the whole process programmatically. This was exactly my question when I wanted to deploy several database diagrams from a database hosted in development environment into a copy of that database hosted by test or UAT (User Acceptance Test) environments. For instance, just assume that there are a bunch of database diagrams created by developers in development environment. So, you’ll have an exact copy of the database structure in test environment. Your testers need to use the database diagrams created by the developers. So far, so good. This part of the challenge is covered in the previous articles. However, we still need to install database diagram support manually and this is what we don’t like! The solution is really easy. Run the following code and you are done! You can also add the following code to the execute SQL task from your SSIS package if you decided to implement the solution in an SSIS package (take a look at “Migrating database diagram by creating a simple SSIS package” No. 8).

The code is as below. Please note that it creates the sysdiagram table as a part of installing the database diagram support:

USE YOUR_DATABASE

 IF OBJECT_ID(N’dbo.sp_upgraddiagrams’) IS NULL and IS_MEMBER(‘db_owner’) = 1

BEGIN

       EXEC sp_executesql N’

       CREATE PROCEDURE dbo.sp_upgraddiagrams

       AS

       BEGIN

              IF OBJECT_ID(N”dbo.sysdiagrams”) IS NOT NULL

                     return 0;

      

              CREATE TABLE dbo.sysdiagrams

              (

                     name sysname NOT NULL,

                     principal_id int NOT NULL, — we may change it to varbinary(85)

                     diagram_id int PRIMARY KEY IDENTITY,

                     version int,

      

                     definition varbinary(max)

                     CONSTRAINT UK_principal_name UNIQUE

                     (

                           principal_id,

                           name

                     )

              );

 

              IF OBJECT_ID(N”dbo.dtproperties”) IS NOT NULL

              begin

                     insert into dbo.sysdiagrams

                     (

                           [name],

                           [principal_id],

                           [version],

                           [definition]

                     )

                     select

                           convert(sysname, dgnm.[uvalue]),

                           DATABASE_PRINCIPAL_ID(N”dbo”),                — will change to the sid of sa

                           0,                                              — zero for old format, dgdef.[version],

                           dgdef.[lvalue]

                     from dbo.[dtproperties] dgnm

                           inner join dbo.[dtproperties] dggd on dggd.[property] = ”DtgSchemaGUID” and dggd.[objectid] = dgnm.[objectid]

                           inner join dbo.[dtproperties] dgdef on dgdef.[property] = ”DtgSchemaDATA” and dgdef.[objectid] = dgnm.[objectid]

                          

                     where dgnm.[property] = ”DtgSchemaNAME” and dggd.[uvalue] like N”_EA3E6268-D998-11CE-9454-00AA00A3F36E_”

                     return 2;

              end

              return 1;

       END

      

 

 

END

 

— This sproc could be executed by any other users than dbo

IF IS_MEMBER(‘db_owner’) = 1

       EXEC dbo.sp_upgraddiagrams;

 

IF OBJECT_ID(N’dbo.sp_helpdiagrams’) IS NULL and IS_MEMBER(‘db_owner’) = 1

BEGIN

       EXEC sp_executesql N’

       CREATE PROCEDURE dbo.sp_helpdiagrams

       (

              @diagramname sysname = NULL,

              @owner_id int = NULL

       )

       WITH EXECUTE AS N”dbo”

       AS

       BEGIN

              DECLARE @user sysname

              DECLARE @dboLogin bit

              EXECUTE AS CALLER;

                     SET @user = USER_NAME();

                     SET @dboLogin = CONVERT(bit,IS_MEMBER(”db_owner”));

              REVERT;

              SELECT

                     [Database] = DB_NAME(),

                     [Name] = name,

                     [ID] = diagram_id,

                     [Owner] = USER_NAME(principal_id),

                     [OwnerID] = principal_id

              FROM

                     sysdiagrams

              WHERE

                     (@dboLogin = 1 OR USER_NAME(principal_id) = @user) AND

                     (@diagramname IS NULL OR name = @diagramname) AND

                     (@owner_id IS NULL OR principal_id = @owner_id)

              ORDER BY

                     4, 5, 1

       END

      

 

 

       GRANT EXECUTE ON dbo.sp_helpdiagrams TO public

       DENY EXECUTE ON dbo.sp_helpdiagrams TO guest

END

 

IF OBJECT_ID(N’dbo.sp_helpdiagramdefinition’) IS NULL and IS_MEMBER(‘db_owner’) = 1

BEGIN

       EXEC sp_executesql N’

       CREATE PROCEDURE dbo.sp_helpdiagramdefinition

       (

              @diagramname sysname,

              @owner_id     int    = null              

       )

       WITH EXECUTE AS N”dbo”

       AS

       BEGIN

              set nocount on

 

              declare @theId             int

              declare @IsDbo             int

              declare @DiagId            int

              declare @UIDFound    int

      

              if(@diagramname is null)

              begin

                     RAISERROR (N”E_INVALIDARG”, 16, 1);

                     return -1

              end

      

              execute as caller;

              select @theId = DATABASE_PRINCIPAL_ID();

              select @IsDbo = IS_MEMBER(N”db_owner”);

              if(@owner_id is null)

                     select @owner_id = @theId;

              revert;

      

              select @DiagId = diagram_id, @UIDFound = principal_id from dbo.sysdiagrams where principal_id = @owner_id and name = @diagramname;

              if(@DiagId IS NULL or (@IsDbo = 0 and @UIDFound <> @theId ))

              begin

                     RAISERROR (”Diagram does not exist or you do not have permission.”, 16, 1);

                     return -3

              end

 

              select version, definition FROM dbo.sysdiagrams where diagram_id = @DiagId ;

              return 0

       END

      

 

 

       GRANT EXECUTE ON dbo.sp_helpdiagramdefinition TO public

       DENY EXECUTE ON dbo.sp_helpdiagramdefinition TO guest

END

 

IF OBJECT_ID(N’dbo.sp_creatediagram’) IS NULL and IS_MEMBER(‘db_owner’) = 1

BEGIN

       EXEC sp_executesql N’

       CREATE PROCEDURE dbo.sp_creatediagram

       (

              @diagramname sysname,

              @owner_id            int    = null,      

              @version             int,

              @definition   varbinary(max)

       )

       WITH EXECUTE AS ”dbo”

       AS

       BEGIN

              set nocount on

      

              declare @theId int

              declare @retval int

              declare @IsDbo       int

              declare @userName sysname

              if(@version is null or @diagramname is null)

              begin

                     RAISERROR (N”E_INVALIDARG”, 16, 1);

                     return -1

              end

      

              execute as caller;

              select @theId = DATABASE_PRINCIPAL_ID();

              select @IsDbo = IS_MEMBER(N”db_owner”);

              revert;

             

              if @owner_id is null

              begin

                     select @owner_id = @theId;

              end

              else

              begin

                     if @theId <> @owner_id

                     begin

                           if @IsDbo = 0

                           begin

                                  RAISERROR (N”E_INVALIDARG”, 16, 1);

                                  return -1

                           end

                           select @theId = @owner_id

                     end

              end

              — next 2 line only for test, will be removed after define name unique

              if EXISTS(select diagram_id from dbo.sysdiagrams where principal_id = @theId and name = @diagramname)

              begin

                     RAISERROR (”The name is already used.”, 16, 1);

                     return -2

              end

      

              insert into dbo.sysdiagrams(name, principal_id , version, definition)

                           VALUES(@diagramname, @theId, @version, @definition) ;

             

              select @retval = @@IDENTITY

              return @retval

       END

      

 

 

       GRANT EXECUTE ON dbo.sp_creatediagram TO public

       DENY EXECUTE ON dbo.sp_creatediagram TO guest

END

 

IF OBJECT_ID(N’dbo.sp_renamediagram’) IS NULL and IS_MEMBER(‘db_owner’) = 1

BEGIN

       EXEC sp_executesql N’

       CREATE PROCEDURE dbo.sp_renamediagram

       (

              @diagramname         sysname,

              @owner_id            int    = null,

              @new_diagramname     sysname

      

       )

       WITH EXECUTE AS ”dbo”

       AS

       BEGIN

              set nocount on

              declare @theId                    int

              declare @IsDbo                    int

             

              declare @UIDFound          int

              declare @DiagId                   int

              declare @DiagIdTarg        int

              declare @u_name                   sysname

              if((@diagramname is null) or (@new_diagramname is null))

              begin

                     RAISERROR (”Invalid value”, 16, 1);

                     return -1

              end

      

              EXECUTE AS CALLER;

              select @theId = DATABASE_PRINCIPAL_ID();

              select @IsDbo = IS_MEMBER(N”db_owner”);

              if(@owner_id is null)

                     select @owner_id = @theId;

              REVERT;

      

              select @u_name = USER_NAME(@owner_id)

      

              select @DiagId = diagram_id, @UIDFound = principal_id from dbo.sysdiagrams where principal_id = @owner_id and name = @diagramname

              if(@DiagId IS NULL or (@IsDbo = 0 and @UIDFound <> @theId))

              begin

                     RAISERROR (”Diagram does not exist or you do not have permission.”, 16, 1)

                     return -3

              end

      

              — if((@u_name is not null) and (@new_diagramname = @diagramname))   — nothing will change

                   return 0;

      

              if(@u_name is null)

                     select @DiagIdTarg = diagram_id from dbo.sysdiagrams where principal_id = @theId and name = @new_diagramname

              else

                     select @DiagIdTarg = diagram_id from dbo.sysdiagrams where principal_id = @owner_id and name = @new_diagramname

      

              if((@DiagIdTarg is not null) and  @DiagId <> @DiagIdTarg)

              begin

                     RAISERROR (”The name is already used.”, 16, 1);

                     return -2

              end          

      

              if(@u_name is null)

                     update dbo.sysdiagrams set [name] = @new_diagramname, principal_id = @theId where diagram_id = @DiagId

              else

                     update dbo.sysdiagrams set [name] = @new_diagramname where diagram_id = @DiagId

              return 0

       END

      

 

 

       GRANT EXECUTE ON dbo.sp_renamediagram TO public

       DENY EXECUTE ON dbo.sp_renamediagram TO guest

END

 

IF OBJECT_ID(N’dbo.sp_alterdiagram’) IS NULL and IS_MEMBER(‘db_owner’) = 1

BEGIN

       EXEC sp_executesql N’

       CREATE PROCEDURE dbo.sp_alterdiagram

       (

              @diagramname sysname,

              @owner_id     int    = null,

              @version      int,

              @definition   varbinary(max)

       )

       WITH EXECUTE AS ”dbo”

       AS

       BEGIN

              set nocount on

      

              declare @theId                    int

              declare @retval            int

              declare @IsDbo                    int

             

              declare @UIDFound          int

              declare @DiagId                   int

              declare @ShouldChangeUID   int

      

              if(@diagramname is null)

              begin

                     RAISERROR (”Invalid ARG”, 16, 1)

                     return -1

              end

      

              execute as caller;

              select @theId = DATABASE_PRINCIPAL_ID();

              select @IsDbo = IS_MEMBER(N”db_owner”);

              if(@owner_id is null)

                     select @owner_id = @theId;

              revert;

      

              select @ShouldChangeUID = 0

              select @DiagId = diagram_id, @UIDFound = principal_id from dbo.sysdiagrams where principal_id = @owner_id and name = @diagramname

             

              if(@DiagId IS NULL or (@IsDbo = 0 and @theId <> @UIDFound))

              begin

                     RAISERROR (”Diagram does not exist or you do not have permission.”, 16, 1);

                     return -3

              end

      

              if(@IsDbo <> 0)

              begin

                     if(@UIDFound is null or USER_NAME(@UIDFound) is null) — invalid principal_id

                     begin

                           select @ShouldChangeUID = 1 ;

                     end

              end

 

              — update dds data               

              update dbo.sysdiagrams set definition = @definition where diagram_id = @DiagId ;

 

              — change owner

              if(@ShouldChangeUID = 1)

                     update dbo.sysdiagrams set principal_id = @theId where diagram_id = @DiagId ;

 

              — update dds version

              if(@version is not null)

                     update dbo.sysdiagrams set version = @version where diagram_id = @DiagId ;

 

              return 0

       END

      

 

 

       GRANT EXECUTE ON dbo.sp_alterdiagram TO public

       DENY EXECUTE ON dbo.sp_alterdiagram TO guest

END

 

IF OBJECT_ID(N’dbo.sp_dropdiagram’) IS NULL and IS_MEMBER(‘db_owner’) = 1

BEGIN

       EXEC sp_executesql N’

       CREATE PROCEDURE dbo.sp_dropdiagram

       (

              @diagramname sysname,

              @owner_id     int    = null

       )

       WITH EXECUTE AS ”dbo”

       AS

       BEGIN

              set nocount on

              declare @theId                    int

              declare @IsDbo                    int

             

              declare @UIDFound          int

              declare @DiagId                   int

      

              if(@diagramname is null)

              begin

                     RAISERROR (”Invalid value”, 16, 1);

                     return -1

              end

      

              EXECUTE AS CALLER;

              select @theId = DATABASE_PRINCIPAL_ID();

              select @IsDbo = IS_MEMBER(N”db_owner”);

              if(@owner_id is null)

                     select @owner_id = @theId;

              REVERT;

             

              select @DiagId = diagram_id, @UIDFound = principal_id from dbo.sysdiagrams where principal_id = @owner_id and name = @diagramname

              if(@DiagId IS NULL or (@IsDbo = 0 and @UIDFound <> @theId))

              begin

                     RAISERROR (”Diagram does not exist or you do not have permission.”, 16, 1)

                     return -3

              end

      

              delete from dbo.sysdiagrams where diagram_id = @DiagId;

      

              return 0;

       END

      

 

 

       GRANT EXECUTE ON dbo.sp_dropdiagram TO public

       DENY EXECUTE ON dbo.sp_dropdiagram TO guest

END

 

IF OBJECT_ID(N’dbo.fn_diagramobjects’) IS NULL and IS_MEMBER(‘db_owner’) = 1

BEGIN

       EXEC sp_executesql N’

       CREATE FUNCTION dbo.fn_diagramobjects()

       RETURNS int

       WITH EXECUTE AS N”dbo”

       AS

       BEGIN

              declare @id_upgraddiagrams        int

              declare @id_sysdiagrams                  int

              declare @id_helpdiagrams          int

              declare @id_helpdiagramdefinition int

              declare @id_creatediagram  int

              declare @id_renamediagram  int

              declare @id_alterdiagram   int

              declare @id_dropdiagram           int

              declare @InstalledObjects  int

 

              select @InstalledObjects = 0

 

              select        @id_upgraddiagrams = object_id(N”dbo.sp_upgraddiagrams”),

                     @id_sysdiagrams = object_id(N”dbo.sysdiagrams”),

                     @id_helpdiagrams = object_id(N”dbo.sp_helpdiagrams”),

                     @id_helpdiagramdefinition = object_id(N”dbo.sp_helpdiagramdefinition”),

                     @id_creatediagram = object_id(N”dbo.sp_creatediagram”),

                     @id_renamediagram = object_id(N”dbo.sp_renamediagram”),

                     @id_alterdiagram = object_id(N”dbo.sp_alterdiagram”),

                     @id_dropdiagram = object_id(N”dbo.sp_dropdiagram”)

 

              if @id_upgraddiagrams is not null

                     select @InstalledObjects = @InstalledObjects + 1

              if @id_sysdiagrams is not null

                     select @InstalledObjects = @InstalledObjects + 2

              if @id_helpdiagrams is not null

                     select @InstalledObjects = @InstalledObjects + 4

              if @id_helpdiagramdefinition is not null

                     select @InstalledObjects = @InstalledObjects + 8

              if @id_creatediagram is not null

                     select @InstalledObjects = @InstalledObjects + 16

              if @id_renamediagram is not null

                     select @InstalledObjects = @InstalledObjects + 32

              if @id_alterdiagram  is not null

                     select @InstalledObjects = @InstalledObjects + 64

              if @id_dropdiagram is not null

                     select @InstalledObjects = @InstalledObjects + 128

             

              return @InstalledObjects

       END

      

 

 

       GRANT EXECUTE ON dbo.fn_diagramobjects TO public

       DENY EXECUTE ON dbo.fn_diagramobjects TO guest

END

 

if IS_MEMBER(‘db_owner’) = 1

BEGIN

       declare @val int

       select @val = 1

       if NOT EXISTS(       select major_id

                                  from sys.extended_properties

                                  where major_id = object_id(N’dbo.sysdiagrams’) and class = 1 and minor_id = 0 and name = N’microsoft_database_tools_support’)

       begin

              exec sp_addextendedproperty N’microsoft_database_tools_support’, @val, ‘SCHEMA’, N’dbo’, ‘TABLE’, N’sysdiagrams’, NULL, NULL

       end

      

       if NOT EXISTS(       select major_id

                                  from sys.extended_properties

                                  where major_id = object_id(N’dbo.sp_upgraddiagrams’) and class = 1 and minor_id = 0 and name = N’microsoft_database_tools_support’)

       begin

              exec sp_addextendedproperty N’microsoft_database_tools_support’, @val, ‘SCHEMA’, N’dbo’, ‘PROCEDURE’, N’sp_upgraddiagrams’, NULL, NULL

       end

      

       if NOT EXISTS(       select major_id

                                  from sys.extended_properties

                                  where major_id = object_id(N’dbo.sp_helpdiagrams’) and class = 1 and minor_id = 0 and name = N’microsoft_database_tools_support’)

       begin

              exec sp_addextendedproperty N’microsoft_database_tools_support’, @val, ‘SCHEMA’, N’dbo’, ‘PROCEDURE’, N’sp_helpdiagrams’, NULL, NULL

       end

      

       if NOT EXISTS(       select major_id

                                  from sys.extended_properties

                                  where major_id = object_id(N’dbo.sp_helpdiagramdefinition’) and class = 1 and minor_id = 0 and name = N’microsoft_database_tools_support’)

       begin

              exec sp_addextendedproperty N’microsoft_database_tools_support’, @val, ‘SCHEMA’, N’dbo’, ‘PROCEDURE’, N’sp_helpdiagramdefinition’, NULL, NULL

       end

      

       if NOT EXISTS(       select major_id

                                  from sys.extended_properties

                                  where major_id = object_id(N’dbo.sp_creatediagram’) and class = 1 and minor_id = 0 and name = N’microsoft_database_tools_support’)

       begin

              exec sp_addextendedproperty N’microsoft_database_tools_support’, @val, ‘SCHEMA’, N’dbo’, ‘PROCEDURE’, N’sp_creatediagram’, NULL, NULL

       end

      

       if NOT EXISTS(       select major_id

                                  from sys.extended_properties

                                  where major_id = object_id(N’dbo.sp_renamediagram’) and class = 1 and minor_id = 0 and name = N’microsoft_database_tools_support’)

       begin

              exec sp_addextendedproperty N’microsoft_database_tools_support’, @val, ‘SCHEMA’, N’dbo’, ‘PROCEDURE’, N’sp_renamediagram’, NULL, NULL

       end

      

       if NOT EXISTS(       select major_id

                                  from sys.extended_properties

                                  where major_id = object_id(N’dbo.sp_alterdiagram’) and class = 1 and minor_id = 0 and name = N’microsoft_database_tools_support’)

       begin

              exec sp_addextendedproperty N’microsoft_database_tools_support’, @val, ‘SCHEMA’, N’dbo’, ‘PROCEDURE’, N’sp_alterdiagram’, NULL, NULL

       end

      

       if NOT EXISTS(       select major_id

                                  from sys.extended_properties

                                  where major_id = object_id(N’dbo.sp_dropdiagram’) and class = 1 and minor_id = 0 and name = N’microsoft_database_tools_support’)

       begin

              exec sp_addextendedproperty N’microsoft_database_tools_support’, @val, ‘SCHEMA’, N’dbo’, ‘PROCEDURE’, N’sp_dropdiagram’, NULL, NULL

       end

      

       if NOT EXISTS(       select major_id

                                  from sys.extended_properties

                                  where major_id = object_id(N’dbo.fn_diagramobjects’) and class = 1 and minor_id = 0 and name = N’microsoft_database_tools_support’)

       begin

              exec sp_addextendedproperty N’microsoft_database_tools_support’, @val, ‘SCHEMA’, N’dbo’, ‘FUNCTION’, N’fn_diagramobjects’, NULL, NULL

       end

END