Программная генерация скриптов для MSSQL

Источник: habrahabr

RedQuark

В силу частых и неупорядоченных изменений базы данных, большим числом пользователей, часто возникает вопросы о истории изменений. Речь не идет о тотально логирование всех изменений, которые происходят с базой в течение дня. Интерес представляют собой снимки структуры БД каждый день после окончания рабочего дня. С помощью 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 Files\Microsoft SQL Server\90\SDK\Assemblies
или
C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies
в зависимости от версии 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. Можно писать автоматическое сравнение объектов базы и отсылку сообщений ответственному лицу по факту произошедших изменений.