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
