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://www.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

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

5 (1)

You rated this post out of 5. Change rating

Related content