Export diagrams

Jonas Gunnarsson, 2019-04-15 (first published: 2018-06-01)

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

Easy to use, exports all diagrams in current database, to a script. Tip: Place it in your repository.

Remarks

The name of diagrams need to be unique in database. The script replace an existing diagram.

Verify existing diagram names first! List the database diagram(s) for current database:

select [name] from dbo.sysdiagrams;

Usage

Run the script against a database and select Results to File.

To restore diagram(s), to another database, just run the saved file.

Note

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

Further reading

Revisions

1.0 2018-05-25 First version
1.1 2018-09-11 Replace existing diagram
1.2 2018-11-09 Skip the use dbName line, so restore can be done to any database.

------------------------------------------------------------------------- -- Name: ExportDiagrams.sql -- Date: 2018-11-09 -- Release: 1.2 -- Summary: -- * Export diagrams in current database to an import script. -- Returns: -- * Script to insert or update all database diagrams. -- Remarks: -- * Script requires that the diagram name is unique. -- (Table constraint 'UK_principal_name' require that principal_id and name is unique.) ------------------------------------------------------------------------- set nocount on; -- Export diagram 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); declare @instanceName sysname; -- Diagram loop declare @diagramName sysname; declare @itemCount int = 0; declare @item int = 0; declare @firstDiagram bit = 1; declare @diagrams table ( Id int identity primary key, [Schema] sysname not null, [Name] sysname not null ); select @instanceName = convert(sysname, serverproperty('InstanceName')); print '-------------------------------------------------------------------------'; print '-- Script to restore all diagrams from database [' + @instanceName + '\' + db_name() + '].'; print '-------------------------------------------------------------------------'; print '' if ( exists ( select 1 from information_schema.tables where table_schema = 'dbo' and table_name = 'sysdiagrams' ) ) begin insert into @diagrams select [Schema] = 'dbo', [Name] = [name] from dbo.sysdiagrams; select @item = min(Id) from @diagrams; select @itemCount = count(Id) from @diagrams; while @item < @itemCount + 1 begin select @diagramName = [Name] from @diagrams where Id = @item; ------------------------------------------------------------------------ -- Export diagram set @principal_id = 0; set @diagram_id = 0; set @version = 0; set @index = 1; set @size = 0; set @chunk = 32; set @line = ''; 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 [' + @instanceName + '\' + db_name() + '].'; print '-------------------------------------------------------------------------'; end else begin print '-------------------------------------------------------------------------'; print '-- Summary: Restore diagram [' + @diagramName + '] from database [' + @instanceName + '\' + db_name() + '].'; print '-------------------------------------------------------------------------'; print 'print ''=== Restoring diagram [' + @diagramName + '] ==='';'; print 'set nocount on;'; if (@firstDiagram = 1) begin print 'declare @id int;'; print 'declare @outputs table (Id int not null);'; end else begin print '-- declare @id int;'; print '-- declare @outputs table (Id int not null);'; print 'delete from @outputs;'; end print 'begin try'; print ' if exists (select [name] from dbo.sysdiagrams where [name] = ''' + @diagramName + ''')'; print ' begin'; print ' set @id = (select top(1) diagram_id from dbo.sysdiagrams where [name] = ''' + @diagramName + ''' order by diagram_id);'; print ' update dbo.sysdiagrams set [principal_id] = ' + cast(@principal_id as varchar(10)) + ', [version] = ' + cast(@version as varchar(10)) + ', [definition] = 0x where diagram_id = @id;'; print ' end'; print ' else'; print ' begin'; print ' 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);'; print ' set @id = (select top(1) Id from @outputs order by Id);'; print ' end'; 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 = @id; -- 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(@id as varchar(10)) + ''. ==='';'; print 'end try'; print 'begin catch'; print ' delete from dbo.sysdiagrams where diagram_id = @id;'; print ' print ''=== '' + error_message() + '' ==='';'; print 'end catch;'; print '-- End of restore diagram [' + @diagramName + '] script.'; print ''; end -- End Export diagram ------------------------------------------------------------------------ set @firstDiagram = 0; set @item += 1; end end print '-- End of restore all diagram script.';