Как я могу написать сценарий базы данных SQL Server?

Question

Как я могу экспортировать диаграммы базы данных SQL Server в виде удобных для разработчиков сценариев SQL?

Под дружественным к разработчикам я имею в виду то, что написано так же, как и человек, пишущий их, а не как много UPDATE Стиль, используемый существующими решениями.

(Обратите внимание, что похожие вопросы на этом сайте, похоже, охватывают только конкретные версии SQL Server или миграцию диаграмм.)

Answer 1

Вот скрипт для этого. Протестировано в SQL Server 2008 R2 и 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);
';

Он в основном экспортирует содержимое sysdiagrams Таблица. Обратите внимание, что он не сохраняет идентификационные номера диаграмм. Это также сохраняет, кто создал диаграммы, но идентификационный номер должен также существовать в целевой базе данных.

Если вы запустите результирующий скрипт на экземпляре сервера, который не имеет объектов схемы базы данных, он все равно должен работать. Однако после этого, чтобы они появились в SSMS, я думаю, что вам нужно будет развернуть узел Диаграммы базы данных и нажать " Да", когда вас попросят создать их.

Это основано на сценарии 2008 года отсюда.

Обратите внимание, что есть подвох! SSMS и другие инструменты Microsoft обрезают полученный текст в наборе результатов, если у вас есть несколько диаграмм. Чтобы получить полный текст, вот скрипт PowerShell для запуска запроса и помещения вывода в буфер обмена:

$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

Заполните базу данных, имя экземпляра и заполнители SQL.

Answer 2

@ Ответ Сэма на 100% правильный и работает (в том числе и против 2019 года), и вы все равно должны делать то, что он говорит: выполнение с использованием Powershell

Его оригинальный сценарий PS включает в себяPauseближе к концу, и у меня возникают ошибки, когда я запускаю скрипт вPowershell ISE(вероятно, из-за собственной наивности)

Итак, вот мой слегка измененный сценарий PS (с уже встроенным SQL), который является почти прямой кражей прекрасной работы @Sam

      $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"

Просто запустите Windows PowerShell ISE, вставьте это в верхнюю панель, обновите строку подключения ~ строка 10. Сохраните сценарий. Запустите скрипт.