How you can set up database diagram help programmatically


Within the earlier article we mentioned about how migrating a database diagram into one other database. On this article I’ll shortly present you the way to set up database diagram help. As talked about within the” How you can copy or migrate database diagrams into one other database” article, we have to set up database diagram help to have the ability to see the migrated database diagrams. We additionally defined a very simple approach to set up database diagram help from SSMS within the “How you can retailer a SQL Server database diagram right into a file and share it with others?” article. Now, assume that we need to migrate the database diagrams into a number of SQL Server cases. It appears that evidently it may be higher if we will implement the entire course of programmatically. This was precisely my query after I needed to deploy a number of database diagrams from a database hosted in improvement setting into a duplicate of that database hosted by take a look at or UAT (Consumer Acceptance Check) environments. For example, simply assume that there are a bunch of database diagrams created by builders in improvement setting. So, you’ll have a precise copy of the database construction in take a look at setting. Your testers want to make use of the database diagrams created by the builders. Up to now, so good. This a part of the problem is roofed within the earlier articles. Nonetheless, we nonetheless want to put in database diagram help manually and that is what we don’t like! The answer is very easy. Run the next code and you might be carried out! You can even add the next code to the execute SQL job out of your SSIS package deal in case you determined to implement the answer in an SSIS package deal (check out “Migrating database diagram by making a easy SSIS package deal” No. 8).

The code is as under. Please word that it creates the sysdiagram desk as part of putting in the database diagram help:

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

              (

                     identify sysname NOT NULL,

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

                     diagram_id int PRIMARY KEY IDENTITY,

                     model int,

      

                     definition varbinary(max)

                     CONSTRAINT UK_principal_name UNIQUE

                     (

                           principal_id,

                           identify

                     )

              );

 

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

              start

                     insert into dbo.sysdiagrams

                     (

                           [name],

                           [principal_id],

                           [version],

                           [definition]

                     )

                     choose

                           convert(sysname, dgnm.[uvalue]),

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

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

                           dgdef.[lvalue]

                     from dbo.[dtproperties] dgnm

                           inside be a part of dbo.[dtproperties] dggd on dggd.[property] = ”DtgSchemaGUID” and dggd.[objectid] = dgnm.[objectid]

                           inside be a part of dbo.[dtproperties] dgdef on dgdef.[property] = ”DtgSchemaDATA” and dgdef.[objectid] = dgnm.[objectid]

                          

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

                     return 2;

              finish

              return 1;

       END

      

 

 

END

 

— This sproc might be executed by some other customers 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 @consumer sysname

              DECLARE @dboLogin bit

              EXECUTE AS CALLER;

                     SET @consumer = USER_NAME();

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

              REVERT;

              SELECT

                     [Database] = DB_NAME(),

                     [Name] = identify,

                     [ID] = diagram_id,

                     [Owner] = USER_NAME(principal_id),

                     [OwnerID] = principal_id

              FROM

                     sysdiagrams

              WHERE

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

                     (@diagramname IS NULL OR identify = @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 visitor

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)

              start

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

                     return -1

              finish

      

              execute as caller;

              choose @theId = DATABASE_PRINCIPAL_ID();

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

              if(@owner_id is null)

                     choose @owner_id = @theId;

              revert;

      

              choose @DiagId = diagram_id, @UIDFound = principal_id from dbo.sysdiagrams the place principal_id = @owner_id and identify = @diagramname;

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

              start

                     RAISERROR (”Diagram doesn’t exist otherwise you do not need permission.”, 16, 1);

                     return -3

              finish

 

              choose model, definition FROM dbo.sysdiagrams the place diagram_id = @DiagId ;

              return 0

       END

      

 

 

       GRANT EXECUTE ON dbo.sp_helpdiagramdefinition TO public

       DENY EXECUTE ON dbo.sp_helpdiagramdefinition TO visitor

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,      

              @model             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(@model is null or @diagramname is null)

              start

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

                     return -1

              finish

      

              execute as caller;

              choose @theId = DATABASE_PRINCIPAL_ID();

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

              revert;

             

              if @owner_id is null

              start

                     choose @owner_id = @theId;

              finish

              else

              start

                     if @theId <> @owner_id

                     start

                           if @IsDbo = 0

                           start

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

                                  return -1

                           finish

                           choose @theId = @owner_id

                     finish

              finish

              — subsequent 2 line just for take a look at, shall be eliminated after outline identify distinctive

              if EXISTS(choose diagram_id from dbo.sysdiagrams the place principal_id = @theId and identify = @diagramname)

              start

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

                     return -2

              finish

      

              insert into dbo.sysdiagrams(identify, principal_id , model, definition)

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

             

              choose @retval = @@IDENTITY

              return @retval

       END

      

 

 

       GRANT EXECUTE ON dbo.sp_creatediagram TO public

       DENY EXECUTE ON dbo.sp_creatediagram TO visitor

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))

              start

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

                     return -1

              finish

      

              EXECUTE AS CALLER;

              choose @theId = DATABASE_PRINCIPAL_ID();

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

              if(@owner_id is null)

                     choose @owner_id = @theId;

              REVERT;

      

              choose @u_name = USER_NAME(@owner_id)

      

              choose @DiagId = diagram_id, @UIDFound = principal_id from dbo.sysdiagrams the place principal_id = @owner_id and identify = @diagramname

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

              start

                     RAISERROR (”Diagram doesn’t exist otherwise you do not need permission.”, 16, 1)

                     return -3

              finish

      

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

                   return 0;

      

              if(@u_name is null)

                     choose @DiagIdTarg = diagram_id from dbo.sysdiagrams the place principal_id = @theId and identify = @new_diagramname

              else

                     choose @DiagIdTarg = diagram_id from dbo.sysdiagrams the place principal_id = @owner_id and identify = @new_diagramname

      

              if((@DiagIdTarg will not be null) and  @DiagId <> @DiagIdTarg)

              start

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

                     return -2

              finish          

      

              if(@u_name is null)

                     replace dbo.sysdiagrams set [name] = @new_diagramname, principal_id = @theId the place diagram_id = @DiagId

              else

                     replace dbo.sysdiagrams set [name] = @new_diagramname the place diagram_id = @DiagId

              return 0

       END

      

 

 

       GRANT EXECUTE ON dbo.sp_renamediagram TO public

       DENY EXECUTE ON dbo.sp_renamediagram TO visitor

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,

              @model      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)

              start

                     RAISERROR (”Invalid ARG”, 16, 1)

                     return -1

              finish

      

              execute as caller;

              choose @theId = DATABASE_PRINCIPAL_ID();

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

              if(@owner_id is null)

                     choose @owner_id = @theId;

              revert;

      

              choose @ShouldChangeUID = 0

              choose @DiagId = diagram_id, @UIDFound = principal_id from dbo.sysdiagrams the place principal_id = @owner_id and identify = @diagramname

             

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

              start

                     RAISERROR (”Diagram doesn’t exist otherwise you do not need permission.”, 16, 1);

                     return -3

              finish

      

              if(@IsDbo <> 0)

              start

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

                     start

                           choose @ShouldChangeUID = 1 ;

                     finish

              finish

 

              — replace dds knowledge               

              replace dbo.sysdiagrams set definition = @definition the place diagram_id = @DiagId ;

 

              — change proprietor

              if(@ShouldChangeUID = 1)

                     replace dbo.sysdiagrams set principal_id = @theId the place diagram_id = @DiagId ;

 

              — replace dds model

              if(@model will not be null)

                     replace dbo.sysdiagrams set model = @model the place diagram_id = @DiagId ;

 

              return 0

       END

      

 

 

       GRANT EXECUTE ON dbo.sp_alterdiagram TO public

       DENY EXECUTE ON dbo.sp_alterdiagram TO visitor

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)

              start

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

                     return -1

              finish

      

              EXECUTE AS CALLER;

              choose @theId = DATABASE_PRINCIPAL_ID();

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

              if(@owner_id is null)

                     choose @owner_id = @theId;

              REVERT;

             

              choose @DiagId = diagram_id, @UIDFound = principal_id from dbo.sysdiagrams the place principal_id = @owner_id and identify = @diagramname

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

              start

                     RAISERROR (”Diagram doesn’t exist otherwise you do not need permission.”, 16, 1)

                     return -3

              finish

      

              delete from dbo.sysdiagrams the place diagram_id = @DiagId;

      

              return 0;

       END

      

 

 

       GRANT EXECUTE ON dbo.sp_dropdiagram TO public

       DENY EXECUTE ON dbo.sp_dropdiagram TO visitor

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

 

              choose @InstalledObjects = 0

 

              choose        @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 will not be null

                     choose @InstalledObjects = @InstalledObjects + 1

              if @id_sysdiagrams will not be null

                     choose @InstalledObjects = @InstalledObjects + 2

              if @id_helpdiagrams will not be null

                     choose @InstalledObjects = @InstalledObjects + 4

              if @id_helpdiagramdefinition will not be null

                     choose @InstalledObjects = @InstalledObjects + 8

              if @id_creatediagram will not be null

                     choose @InstalledObjects = @InstalledObjects + 16

              if @id_renamediagram will not be null

                     choose @InstalledObjects = @InstalledObjects + 32

              if @id_alterdiagram  will not be null

                     choose @InstalledObjects = @InstalledObjects + 64

              if @id_dropdiagram will not be null

                     choose @InstalledObjects = @InstalledObjects + 128

             

              return @InstalledObjects

       END

      

 

 

       GRANT EXECUTE ON dbo.fn_diagramobjects TO public

       DENY EXECUTE ON dbo.fn_diagramobjects TO visitor

END

 

if IS_MEMBER(‘db_owner’) = 1

BEGIN

       declare @val int

       choose @val = 1

       if NOT EXISTS(       choose major_id

                                  from sys.extended_properties

                                  the place major_id = object_id(N’dbo.sysdiagrams’) and class = 1 and minor_id = 0 and identify = N’microsoft_database_tools_support’)

       start

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

       finish

      

       if NOT EXISTS(       choose major_id

                                  from sys.extended_properties

                                  the place major_id = object_id(N’dbo.sp_upgraddiagrams’) and class = 1 and minor_id = 0 and identify = N’microsoft_database_tools_support’)

       start

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

       finish

      

       if NOT EXISTS(       choose major_id

                                  from sys.extended_properties

                                  the place major_id = object_id(N’dbo.sp_helpdiagrams’) and class = 1 and minor_id = 0 and identify = N’microsoft_database_tools_support’)

       start

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

       finish

      

       if NOT EXISTS(       choose major_id

                                  from sys.extended_properties

                                  the place major_id = object_id(N’dbo.sp_helpdiagramdefinition’) and class = 1 and minor_id = 0 and identify = N’microsoft_database_tools_support’)

       start

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

       finish

      

       if NOT EXISTS(       choose major_id

                                  from sys.extended_properties

                                  the place major_id = object_id(N’dbo.sp_creatediagram’) and class = 1 and minor_id = 0 and identify = N’microsoft_database_tools_support’)

       start

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

       finish

      

       if NOT EXISTS(       choose major_id

                                  from sys.extended_properties

                                  the place major_id = object_id(N’dbo.sp_renamediagram’) and class = 1 and minor_id = 0 and identify = N’microsoft_database_tools_support’)

       start

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

       finish

      

       if NOT EXISTS(       choose major_id

                                  from sys.extended_properties

                                  the place major_id = object_id(N’dbo.sp_alterdiagram’) and class = 1 and minor_id = 0 and identify = N’microsoft_database_tools_support’)

       start

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

       finish

      

       if NOT EXISTS(       choose major_id

                                  from sys.extended_properties

                                  the place major_id = object_id(N’dbo.sp_dropdiagram’) and class = 1 and minor_id = 0 and identify = N’microsoft_database_tools_support’)

       start

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

       finish

      

       if NOT EXISTS(       choose major_id

                                  from sys.extended_properties

                                  the place major_id = object_id(N’dbo.fn_diagramobjects’) and class = 1 and minor_id = 0 and identify = N’microsoft_database_tools_support’)

       start

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

       finish

END

 

 



Supply hyperlink

Leave a Reply

Your email address will not be published. Required fields are marked *