В свой предыдущей статье я показал, как можно использовать мастер 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.