Export diagrams

,

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.';

Rate

4.67 (3)

Share

Share

Rate

4.67 (3)