В свой предыдущей статье я показал, как можно использовать мастер SQL Server Management Studio для генерации скриптов для различных объектов SQL Server. Мастер обладает большим набором возможностей, но также и рядом недостатков. Например, мы видели, что нельзя легко сгенерировать скрипты для всех объектов из определенной схемы, кроме как вручную указать все эти объекты. В этой статье мы рассмотрим другой подход к генерации скриптов – программный. Для этого мы будем использовать Powershell, мощное и гибкое средство для управления любыми Windows машинами или сервисами, в том числе и SQL Server.

Самый простой способ – запустить Powershell прямо из SSMS.

У вас откроется консоль Powershell, в которой уже будет загружен модуль SQLPS и открыт путь до нашей базы данных. Здесь можно просматривать и работать с объектами также, как и с файлами и папками. Например, с помощью следующего набора команд мы перейдем к таблицам в нашей базе данных, получим список всех таблиц в переменную и для первой таблицы в списке сгенерируем скрипт.

# Переходим к списку таблиц
cd Tables

# Считываем все таблицы в массив
$tables = Get-ChildItem

# Выводим информацию о первом элементе
$tables[1]

# Для первой таблицы генерируем скрипт
$tables[1].Script() 

У вас должно получиться что-то подобное, как на скриншоте снизу.

Но, если мы хотим, например, сгенерировать не скрипт создания, а скрипт удаления указанной таблицы. Для этого потребуется создать объект класса Microsoft.SqlServer.Management.Smo.ScriptingOptions, указать у него определенные свойства (а их у объекта большое множество) и снова вызвать метод Script у таблицы, в который в качестве параметра передать объект со свойствами.

# Создаем новый объект класса Microsoft.SqlServer.Management.Smo.ScriptingOptions
$script_options = New-Object ('Microsoft.SqlServer.Management.Smo.ScriptingOptions')

# У этого объекта выставляем свойство ScriptDrops в значение true
$script_options.ScriptDrops = $true

# Снова запускаем метод Script с указанными опциями
$tables[1].Script($script_options) 

Мы даже можем указать имя файла и сохранить сгенерированный скрипт в файле.

# Указываем файл, куда будет сохранен скрипт
$file_name = "C:\Temp\MyScript.sql"

# Перенаправляем вывод с консоли в указанный файл
$tables[1].Script($script_options) > $file_name 

Или используя цикл, обойти все объекты по определенному условию и сохранить скрипты в нужном месте.

# Для всех таблиц, имя которых начинается на Pro, сгенерировать скрипты с опциями, указанными в $script_oprions и выложить скрипт для каждого для каждого отдельного объекта в папку C:\Temp в формате <Имя схемы>.<Имя таблицы>.sql
foreach ($t in $tables | Where-Object { $_.Name.StartsWith("Pro") }) { $t.Script($script_options) > "C:\Temp\$($t.Schema).$($t.Name).sql" } 

Как мы видим, набор возможностей практически не ограничен за одним исключением. Набивать эти команды в консоли крайне неудобно. Гораздо удобнее написать отдельный скрипт и вызывать его по мере необходимости. Хочу привести пример отдельного скрипта на Powershell для генерации скриптов создания всех таблиц в базе с ограничениями, внешними ключами и т.п. в один большой скрипт с разделителями.

# Загружаем модуль для работы с SMO объектами.
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

# Объявляем переменные и указываем имя сервера, имя базы данных и путь до файла, куда будет сохранен скрипт создания всех таблиц.
$srv_name = "(local)"
$db_name = "AdventureWorks2012"
$file_name = "C:\Temp\CreateAllTables.sql"

# Объявляем объект класса Microsoft.SqlServer.Management.Smo.Server.
$srv = New-Object('Microsoft.SqlServer.Management.Smo.Server') $srv_name

# Получаем объект нужной нам базы данных.
$db = $srv.Databases[$db_name]

# Создаем объект класса Microsoft.SqlServer.Management.Smo.Scripter, который будет выполнять всю работу по созданию скриптов.
$scripter = New-Object('Microsoft.SqlServer.Management.Smo.Scripter') $srv

# Указываем, что в скрипт нужно включать все DRI объекты (Declarative Referential Integrity: ограничения, внешние ключи и т.п.).
$scripter.Options.DriAll = $true
# Включать в скрипт создание индексов.
$scripter.Options.Indexes = $true
# Включать в скрипт добавление расширенных свойств.
$scripter.Options.ExtendedProperties = $true

# Указывать в скрипте разделитель GO между командами создания объектов.
$scripter.Options.ScriptBatchTerminator = $true
$scripter.Options.NoCommandTerminator = $false

# Указываем, что сохранять скрипт необходимо в файл.
$scripter.Options.FileName = $file_name
$scripter.Options.ToFileOnly = $true
$scripter.Options.AppendToFile=$true

# Для всех таблиц, имя которых начинается на Pro, сгенерировать скрипты и добавить их в указанный файл.
foreach ( $t in $db.Tables | Where-Object { $_.Name.StartsWith("Pro") } ) {
    $scripter.Script($t)
}

На этом все. Я постарался привести максимально полезные примеры, от которых вы сможете отталкиваться при написании своих скриптов на Powershell.