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.

Answer 2

@Sam's answer is 100% correct and works (against 2019, too), and you still must do as he says: executing using Powershell

His original PS script features a Pause near the end, and that bugs-out for me when I run the script in Powershell ISE (probably due to my own naivete)

So, here's my slightly changed PS script (with the SQL embedded already) that is a near direct steal of @Sam's lovely work

$ErrorActionPreference = "Stop"

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=localhost;Initial Catalog=MySpecialDataBase;Integrated Security=SSPI")
$connection.Open()
$command = $connection.CreateCommand() 
$command.CommandText = @"
            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);
            ';
"@

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

$result | Set-Clipboard
echo "Your SQL Diagram was successfully scripted out and copied to the clipbaord"

Just launch your Windows PowerShell ISE, paste this into the top pane, update the connection string ~ line 10. Save the script. Run the script.