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