How can I script SQL Server database diagrams?

Question

How can I export SQL Server database diagrams as developer-friendly SQL scripts?

By developer-friendly, I mean written in a way similar to the way a human would write them as opposed to the messy many-UPDATEs style used by existing solutions.

(Note that similar questions on this site only seem to cover specific versions of SQL Server or migration of diagrams.)

Answer 1

Here's a script to do this. Tested in SQL Server 2008 R2 and 2012.

DECLARE @values nvarchar(max);
SET @values = 
(
    SELECT '
        (''' + REPLACE(name, '''', '''''') + ''', ' + CAST(principal_id AS VARCHAR(100)) +', ' + CAST(version AS VARCHAR(100)) + ', ' + sys.fn_varbintohexstr(definition) + '),'
    FROM sysdiagrams
    FOR XML PATH(''), TYPE
).value('.', 'nvarchar(max)');
SET @values = LEFT(@values, LEN(@values) - 1);

SELECT
'IF OBJECT_ID(N''dbo.sysdiagrams'') IS NULL
    CREATE TABLE dbo.sysdiagrams
    (
        name sysname NOT NULL,
        principal_id int NOT NULL,
        diagram_id int PRIMARY KEY IDENTITY,
        version int,

        definition varbinary(max)
        CONSTRAINT UK_principal_name UNIQUE
        (
            principal_id,
            name
        )
    );

MERGE sysdiagrams AS Target
    USING
    (
        VALUES' + @values + '
    ) AS Source (name, principal_id, version, definition)
    ON Target.name = Source.name
        AND Target.principal_id = Source.principal_id
    WHEN MATCHED THEN
        UPDATE SET version = Source.version, definition = Source.definition
    WHEN NOT MATCHED BY Target THEN
        INSERT (name, principal_id, version, definition)
        VALUES (name, principal_id, version, definition);
';

It basically exports the contents of the sysdiagrams table. Note that it does not retain the diagrams' id numbers. It also retains who created the diagrams, but the id number should also exist in the target database.

If you run the resultant script on a server instance that doesn't have the database diagramming objects, it should still work. However, after doing this, in order for them to appear in SSMS, I think you'll need to expand the Database Diagrams node and click Yes when asked to create them.

This is based on the 2008 script from here.

Note that there is a catch! SSMS and other Microsoft tools truncate the resulting text in the result set if you have more than a few diagrams. To get the full text, here's a PowerShell script to run the query and put the output in the clipboard:

$ErrorActionPreference = "Stop"

function Pause([string]$message) {
    Write-Host $message
    $host.UI.RawUI.ReadKey("NoEcho,IncludeKeyDown") | Out-Null
}

function Set-Clipboard {
    $input | PowerShell -NoProfile -STA -Command {
        Add-Type -AssemblyName "System.Windows.Forms"
        [Windows.Forms.Clipboard]::SetText($input)
    }
}

$connection = New-Object System.Data.SqlClient.SqlConnection ("Data Source=DATABASE_INSTANCE;Initial Catalog=DATABASE;Integrated Security=SSPI")
$connection.Open()
$command = $connection.CreateCommand() 
$command.CommandText = @"
--SQL CODE
"@

$command.CommandTimeout = 60
$result = $command.ExecuteScalar()
$command.Dispose()
$connection.Dispose()

Pause "Press any key to copy the resulting SQL to the clipboard..."
$result | Set-Clipboard

Fill in the database, instance name, and SQL placeholders.