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

    В силу частых и неупорядоченных изменений базы данных, большим числом пользователей, часто возникает вопросы о истории изменений. Речь не идет о тотально логирование всех изменений, которые происходят с базой в течение дня. Интерес представляют собой снимки структуры БД каждый день после окончания рабочего дня. С помощью 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. Можно писать автоматическое сравнение объектов базы и отсылку сообщений ответственному лицу по факту произошедших изменений.
    Код можно посмотреть здесь.
    Основой материала послужила статья: Generate Scripts for database objects with SMO for SQL Server
    AdBlock has stolen the banner, but banners are not teeth — they will be back

    More
    Ads

    Comments 5

      +1
      Супер. Мега спасибо. Крайне полезная штука.
      • UFO just landed and posted this here
          0
          Насчет удобства или неудобства.

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

          Чтобы это сделать берется дата на которую таблица (код) были правильными, и потом уже ищется то ли по альтер скриптам, то ли по переписке, как это произошло.
          0
          вот к стати неплохую утилиту нашел https://github.com/sethreno/schemazen/releases
          работает быстро.

          Пример вызывающего скрипта:

          set r=D:\DATA\scripts
          set s=SERVERNAME\DEV14
          set db=DM

          set d=%r%\%db%-%date:~10%-%date:~4,2%-%date:~7,2%_%time:~0,2%-%time:~3,2%-%time:~6,2%
          D:\DATA\scripts\SchemaZen.exe script --server %s% --database %db% --scriptDir %d%

          вставляем его в sql agent чтобы тот периодически скриптовал БД в отдельную папку с датой в названии.
            0
            Я сделал проще. Включается трассировка с фильтрами по CREATE, ALTER, DROP и записи в файл на шару. Стартует джобом при запуске скуля. Т.к. таких событий не много, то ресурсов практически не занимает. При необходимости разбора полетов, видим все изменения.

            Only users with full accounts can post comments. Log in, please.