Copy SQL Server Diagram

Problem:

A team is developing an application. Each developer works on a copy of the DB (as should)

When the DB Schema is changed a script is used to roll-out the changes.

But what about the Database Diagram? How can each developer have an updated Schema?

Solution

USE DATABASE_NAME
Replace 'Diagram_Name' with the name of the diagram we want to copy.
Query => Results To => Results to text
Run the script:

DECLARE @line NVARCHAR(MAX)=''
,@index INT=1
,@chunk INT=32
,@diagram_id INT=(SELECT diagram_id FROM sysdiagrams WHERE name='Diagram_Name')
,@newId INT=0;
DECLARE @size INT=(SELECT DATALENGTH(definition) FROM sysdiagrams WHERE diagram_id=@diagram_id);

SELECT @line = 'INSERT INTO sysdiagrams ([name], [principal_id], [version], [definition])'
            + ' VALUES (''' + [name] + ''', '+ CAST (principal_id AS VARCHAR(100))+', '+CAST (version AS VARCHAR(100))+', 0x);'
                    FROM sysdiagrams WHERE diagram_id = @diagram_id
PRINT 'SET NOCOUNT ON'
PRINT 'DECLARE @id INT;'
PRINT @line
PRINT 'SET @id=SCOPE_IDENTITY();'
WHILE @index < @size BEGIN
 SELECT @line =  
  'UPDATE sysdiagrams SET [definition] .Write ('
  + ' ' + UPPER(sys.fn_varbintohexstr (SUBSTRING (definition, @index, @chunk)))
  + ', null, 0) WHERE diagram_id = @id;' -- index:' + CAST(@index AS VARCHAR(100))
 FROM    sysdiagrams 
 WHERE    diagram_id = @diagram_id
 PRINT @line
 SET @index = @index + @chunk
END

• In the results windows an UPDATE_SCRIPT is output that can recreate the Database Diagram.
• Send the UPDATE_SCRIPT to target computer.
• Open SQL Server Management Studio.
• New Query
• Select target Database
• If there is no diagram click on Database_Diagrams and confirm to let Management Studio create sysdiagrams table.
• If an older version of the diagram exists, delete it.
• Paste UPDATE_SCRIPT
• RUN UPDATE_SCRIPT
• Refresh Database to see the created Diagram.
• You are done.

Credits: http://www.conceptdevelopment.net/Database/ScriptDiagram2008/