Code reuse

There are two types of projects I am involved in these days.

Custom enterprise applications where we design the database and manage it.
Web applications, that we host, where each customer has his own copy of the database.

In both cases there are many copies of the database installed.

Developer databases
Test database (both internal and customer)
Production database

It is obvious that these databases should be under some Version Control so that can be easily synchronized.
Usually when a database is upgraded, a new version of the application is issued. If only one application is using the database then application-database sync is easy.
When multiple applications are using the same database then this problem is quite difficult to solve.
This is my solution to database versioning problem:

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/

Code reuse

prettify

baseline

Database Version Control

Copy SQL Server Diagram

Problem:

Solution