В силу частых и неупорядоченных изменений базы данных, большим числом пользователей, часто возникает вопросы о истории изменений. Речь не идет о тотально логирование всех изменений, которые происходят с базой в течение дня. Интерес представляют собой снимки структуры БД каждый день после окончания рабочего дня. С помощью SQL Server Management Studio можно сгенерировать скрипты, но поштучно или все сразу. Полную свободу действий можно получить использовав набор библиотек от SQL Server Management Studio в вашем .NET приложение. Описание программы генерации скриптов: таблиц, представлений, процедур далее.
Присоединение библиотек
Потребуются классы из пространств имен:
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Sdk.Sfc;
Библиотеки, которые их содержат имеют такие же названия и находятся в папке:
C:Program FilesMicrosoft SQL Server90SDKAssemblies
или
C:Program FilesMicrosoft SQL Server100SDKAssemblies
в зависимости от версии SQL Server.
Выполняемый сценарий
Генератор скриптов создает отдельные папки Tables, Views, Procedures в указанной директории. Создает скрипты создания объектов и сохраняет в отдельных файлах в соответствующих папках. Для таблиц генерация производиться с учетом зависимостей(ключи, индексы и т.д.) и без. Создает общий файл однотипных скриптов в указанной директории.
//Создается экземпляр сервера
Server myServer = new Server(@"myServ");
//Аутентификация Windows
myServer.ConnectionContext.LoginSecure = true;
//Открыть соединение
myServer.ConnectionContext.Connect();
//Директория создается автоматически, с новой папкой на каждый день
string dir = Application.StartupPath +@""+ DateTime.Now.Day.ToString() + "_" + DateTime.Now.Month.ToString() + "_" + DateTime.Now.Year.ToString();
Directory.CreateDirectory(dir);
//Генерация таблиц, описание ниже
GenerateTableScript(myServer,dir);
//Генерация процедур, описание ниже
GenerateProceduresScript(myServer, dir);
//Генерация представлений, описание ниже
GenerateViewScript(myServer, dir);
//Закрыть соединение
myServer.ConnectionContext.Disconnect();
Генерация скриптов для таблиц
private static void GenerateTableScript(Server myServer, string path)
{
Directory.CreateDirectory(path + @"Tables");
string text = "";
string textWithDependencies = "";
//Создаем экземпляр класса, который будет генерировать скрипты
Scripter scripter = new Scripter(myServer);
//Создаем экземпляр класса базы данных, "ZZZ" - название базы данных
Database myAdventureWorks = myServer.Databases["ZZZ"];
//Создаем экземпляр класса настроек генерации скриптов
ScriptingOptions scriptOptions = new ScriptingOptions();
//Функциональность свойств у класса настроек генерации легко определяема
//Не создавать скрипт с Drop
scriptOptions.ScriptDrops = false;
//Не включать скрипт с If Not Exists
scriptOptions.IncludeIfNotExists = false;
//Перебираем все таблицы
foreach (Table myTable in myAdventureWorks.Tables)
{
//Получаем sql запрос на основание выбранных параметров
StringCollection tableScripts = myTable.Script(scriptOptions);
//Переменная для объединения строк
string newSql = "";
//Объединяем строки
foreach (string script in tableScripts)
{
newSql = newSql + script;
text = text + script;
}
//Записываем в файл скрипт создания таблицы без зависимостей
File.WriteAllText(path + @"Tables" + myTable.Name + ".sql", newSql);
//Определяем новые параметры генерации
scriptOptions.DriAllConstraints = true;
scriptOptions.DriAllKeys = true;
scriptOptions.DriDefaults = true;
scriptOptions.DriForeignKeys = true;
scriptOptions.DriIndexes = true;
scriptOptions.DriNonClustered = true;
scriptOptions.DriPrimaryKey = true;
scriptOptions.DriUniqueKeys = true;
tableScripts = myTable.Script(scriptOptions);
newSql = "";
foreach (string script in tableScripts)
{
newSql = newSql + script;
textWithDependencies = text + script;
}
//Записываем в файл скрипт создания таблицы с зависимостями
File.WriteAllText(path + @"Tables" + myTable.Name + "_WithDependencies.sql", newSql);
}
//Записываем общие объединяющие файлы
File.WriteAllText(path + @"" + "AllTable_WithDependencies.sql", text);
File.WriteAllText(path + @"" + "AllTable.sql", text);
}
Генерация скриптов для представлений
private static void GenerateViewScript(Server myServer, string path)
{
Directory.CreateDirectory(path + @"View");
string text = "";
Scripter scripter = new Scripter(myServer);
Database myAdventureWorks = myServer.Databases["ZZZ"];
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.ScriptDrops = false;
scriptOptions.IncludeIfNotExists = false;
foreach (Microsoft.SqlServer.Management.Smo.View myView in myAdventureWorks.Views)
{
StringCollection ProcedureScripts = myView.Script(scriptOptions);
ProcedureScripts = myView.Script();
string newSql = "";
foreach (string script in ProcedureScripts)
{
newSql = newSql + script;
text = text + script;
}
File.WriteAllText(path + @"Views" + myView.Name + ".sql", newSql);
}
File.WriteAllText(path + @"" + "AllView.sql", text);
}
Генерация скриптов для процедур
private static void GenerateProceduresScript(Server myServer, string path)
{
Directory.CreateDirectory(path + @"Procedures");
string text = "";
Scripter scripter = new Scripter(myServer);
Database myAdventureWorks = myServer.Databases["ZZZ"];
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.ScriptDrops = false;
scriptOptions.IncludeIfNotExists = false;
foreach (StoredProcedure myProcedure in myAdventureWorks.StoredProcedures)
{
StringCollection ProcedureScripts = myProcedure.Script(scriptOptions);
ProcedureScripts = myProcedure.Script();
string newSql = "";
foreach (string script in ProcedureScripts)
{
newSql = newSql + script;
text = text + script;
}
File.WriteAllText(path + @"Procedures" + myProcedure.Name + ".sql", newSql);
}
File.WriteAllText(path + @"" + "AllProcedure.sql", text);
}
Заключение
Описанная функциональность может быть полезна для логирования изменений в структуре баз данных, автоматической фильтрации выгружаемых объектов по названию. Например, префикс в названиях может указывать на отдельное направление в проекте. Общие и отдельные скрипты можно складывать в SVN. Можно писать автоматическое сравнение объектов базы и отсылку сообщений ответственному лицу по факту произошедших изменений.
Код можно посмотреть здесь.
Основой материала послужила статья: Generate Scripts for database objects with SMO for SQL Server
Автор: RedQuark