Technical Article

Generate scripts for SQL Server Management Studio diagrams

,

The procedure generates an import script for SQL Server Management Studio diagrams.

Easy to use, takes one diagram name and an optional parameter, if is first diagram to script.

List the database diagram(s) for current database: 

select * from sysdiagrams;

The script is based on several other scripts, see the References section.

Example of usage: 

execute tool.ScriptDiagram 'First', 1;
execute tool.ScriptDiagram 'Second', 0;
execute tool.ScriptDiagram 'Third', 0;

Note

Objects required to use database diagrams is added for owner, when clicking on Database Diagrams in SSMS.

Further reading

References

Revisions

  • 1.3 2017-03-22 Fixed links and use only tab in code
  • 1.4 2017-10-23 cleanup procedure
  • 1.5 2018-05-25 cleanup procedure, no dates
-------------------------------------------------------------------------
-- Prepare
-------------------------------------------------------------------------
set ansi_nulls on;
go

set quoted_identifier on;
go

if not exists (
		select 1
		from sys.schemas
		where name = N'tool'
		)
begin
	execute (N'create schema tool');

	execute sys.sp_addextendedproperty @name = N'MS_Description',
		@value = N'Schema for maintenance tools.',
		@level0type = N'SCHEMA',
		@level0name = N'tool';
end
go

if (object_id(N'tool.ScriptDiagram', N'P') is not null)
begin
	drop procedure tool.ScriptDiagram;
end
go

-------------------------------------------------------------------------
-- Name: tool.ScriptDiagram
-- Date: 2018-05-25
-- Release: 1.5
-- Summary:
--   * Generate scripts for SQL Server diagrams.
-- Param:
--   @diagramName
--     * The name of the diagram.
--   @firstDiagram
--     * If it is the first diagram to build. (1)
-- References:
--   * Generate scripts for SQL Server Management Studio diagrams
--     - http://qa.sqlservercentral.com/scripts/Diagram/154850/
-- Returns:
--   * Script to insert the database diagram.
-------------------------------------------------------------------------
create procedure tool.ScriptDiagram @diagramName varchar(128),
	@firstDiagram bit = 1
as
begin
	set nocount on;

	declare @principal_id int = 0;
	declare @diagram_id int = 0;
	declare @version int = 0;
	declare @index int = 1;
	declare @size int = 0;
	declare @chunk int = 32;
	declare @line varchar(max);

	select @principal_id = principal_id,
		@diagram_id = diagram_id,
		@version = [version],
		@size = datalength([definition])
	from dbo.sysdiagrams
	where name = @diagramName;

	if @diagram_id is null
	begin
		print '-------------------------------------------------------------------------';
		print '-- Error: Diagram name [' + @diagramName + '] could not be found in [' + db_name() + '].';
		print '-------------------------------------------------------------------------';
	end
	else
	begin
		print '-------------------------------------------------------------------------';
		print '-- Summary: Restore diagram [' + @diagramName + '] from database [' + db_name() + '].';
		print '-------------------------------------------------------------------------';
		print 'print ''=== Restoring diagram [' + @diagramName + '] ==='';';
		print 'set nocount on;';

		if (@firstDiagram = 1)
		begin
			print 'declare @newid int;';
			print 'declare @outputs table (Id int not null);';
		end
		else
		begin
			print '-- declare @newid int;';
			print '-- declare @outputs table (Id int not null);';
			print 'delete from @outputs;';
		end

		print 'begin try';

		select @line = '    insert into dbo.sysdiagrams ([name], [principal_id], [version], [definition])' + ' output inserted.diagram_id into @outputs' + ' values (''' + @diagramName + ''', ' + cast(@principal_id as varchar(10)) + ', ' + cast(@version as varchar(10)) + ', 0x);'
		from dbo.sysdiagrams
		where diagram_id = @diagram_id;

		print @line;
		print '    set @newid = (select top(1) Id from @outputs order by Id);';
		print 'end try';
		print 'begin catch';
		print '    print ''=== '' + error_message() + '' ==='';';
		print '    return;';
		print 'end catch;';
		print '';
		print 'begin try';

		while @index < @size
		begin
			select @line = '    update dbo.sysdiagrams set definition.write(' + convert(varchar(66), substring("definition", @index, @chunk), 1) + ', null, 0) where diagram_id = @newid; -- index:' + cast(@index as varchar(10))
			from dbo.sysdiagrams
			where diagram_id = @diagram_id;

			print @line;

			set @index = @index + @chunk;
		end

		print '';
		print '    print ''=== Diagram [' + @diagramName + '] restored at diagram_id='' + cast(@newid as varchar(10)) + ''. ==='';';
		print 'end try';
		print 'begin catch';
		print '    delete from dbo.sysdiagrams where diagram_id = @newid;';
		print '    print ''=== '' + error_message() + '' ==='';';
		print 'end catch;';
		print '-- End of restore diagram [' + @diagramName + '] script.';
		print '';
	end
end

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

5 (1)

You rated this post out of 5. Change rating

Share

Tags

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

5 (1)

You rated this post out of 5. Change rating

Related content