скрипты создания таблиц базы данных
Как сгенерировать SQL скрипт создания объектов и данных в Microsoft SQL Server?
Привет! Сегодня мы поговорим о том, как можно сгенерировать SQL скрипты создания объектов базы данных Microsoft SQL Server, включая сами данные, стандартными средствами SQL Server Management Studio (SSMS).
Что такое SQL скрипт объекта базы данных?
SQL скрипт объекта базы данных – это SQL инструкция, с помощью которой создается этот объект, сохраненная в текстовом файле.
Такой SQL скрипт можно открыть любым текстовым редактором, скопировать текст SQL запроса и выполнить, например, в среде SQL Server Management Studio, таким образом, создав объект базы данных, не разрабатывая соответствующие SQL инструкции самостоятельно.
Также SQL скрипты можно открыть специальными программами, которые умеют работать с SQL скриптами, и выполнять их на базе данных.
Что могут содержать SQL скрипты?
SQL скрипты объектов базы данных могут содержать:
Для чего могут потребоваться SQL скрипты объектов базы данных?
SQL скрипты объектов базы данных могут потребоваться администраторам или разработчикам, например, для того, чтобы в случае необходимости иметь возможность быстро восстановить эти объекты.
Или для того, чтобы передать эти SQL скрипты другому администратору, разработчику или заказчику, чтобы он создал подобные объекты на своем экземпляре SQL Server.
Таким образом, такие SQL скрипты необходимы для хранения копий SQL инструкций, с помощью которых создавались объекты базы данных.
Как создать SQL скрипт объекта базы данных в Microsoft SQL Server?
Так как SQL скрипт – это обычный текстовый файл, его можно создать вручную, например, когда Вы разрабатываете объект БД, Вы просто сохраняете SQL инструкцию в файл, и добавляете в него по мере необходимости другие SQL инструкции.
Однако также возможно автоматически сгенерировать SQL скрипты объектов базы данных специальными инструментами, например, в среде SQL Server Management Studio (SSMS). А как это делается, я сейчас и покажу.
Заметка! Если Вас интересует SQL и T-SQL, рекомендую посмотреть мои видеокурсы по T-SQL, с помощью которых Вы «с нуля» научитесь работать с SQL и программировать с использованием языка T-SQL в Microsoft SQL Server.
Создание SQL скрипта объекта базы данных Microsoft SQL Server
В качестве исходных данных у меня будет база данных TestDB и таблица Goods, SQL скрипт которой мне и нужно создать. При этом мне необходимо, чтобы скрипт включал не только определение инструкции CREATE, но и данные, которые содержит эта таблица.
В качестве инструмента я буду использовать SQL Server Management Studio.
Итак, давайте начнем.
Шаг 1 – Запускаем SSMS
Сначала запускаем среду SQL Server Management Studio любым удобным для Вас способом, иными словами, никаких особых манипуляций с открытием SSMS выполнять не требуется.
Шаг 2 – Запускаем задачу «Сформировать скрипты»
Далее в обозревателе объектов находим нужную нам базу данных, затем открываем контекстное меню правой кнопкой мыши, ищем пункт «Задачи», и выбираем задачу «Сформировать скрипты».
В итоге запустится мастер создания скриптов. В окне «Введение» можем сразу нажать «Далее».
Шаг 3 – Выбираем объекты для включения в SQL скрипт
После этого нам нужно выбрать объекты базы данных, которые необходимо включить в SQL скрипт. При этом нам доступно два варианта:
Так как мне нужно сохранить только одну таблицу, я выбираю второй вариант и отмечаю нужную таблицу, т.е. в моем случае Goods.
Шаг 4 – Задание параметров SQL скрипта
Теперь мы можем указать параметры формирования скрипта, в частности, каким образом мы хотим получить скрипт.
Доступно 3 способа:
Также есть и дополнительные параметры, чтобы их открыть, необходимо нажать на соответствующую кнопку.
В дополнительных параметрах мы можем более тонко настроить принцип формирования SQL скрипта, например, для того чтобы скрипт включал еще и SQL инструкции создания данных, необходимо в параметрах отметить соответствующий пункт, т.е. указать «Схема и данные».
Также Вы можете включить в скрипты инструкции DROP на случай, если Вам нужно пересоздать объекты.
После того как все параметры заданы, нажимаем «ОК», а после для продолжения кнопку «Далее».
Шаг 5 – Проверка параметров и запуск процесса создания скрипта
На данном шаге все проверяем и запускаем сам процесс формирования скрипта, т.е. нажимаем «Далее».
Шаг 6 – Завершение процесса и результат
Когда процесс будет завершен, программа сообщит Вам об этом, нажимаем «Готово».
Если его открыть, например, текстовым редактором, то можно посмотреть на все SQL инструкции. Они будут выглядеть примерно следующим образом.
Видео-инструкция
На сегодня это все, надеюсь, материал был Вам полезен, пока!
Пример проектирования простой базы данных в MS SQL
В качестве примера спроектируем несложную базу данных информационной системы кинотеатра. При этом, решим следующие задачи:
1 Анализ предметной области
Зачастую, кинотеатр состоит из нескольких залов разной конфигурации, а посетителю предоставляется возможность выбора билета, для этого ему отображается текущее состояние зала. Выбранные места посетитель сообщает кассиру, который вводит их в систему и места помечаются как «проданные». Это «основной» сценарий использования информационной системы, однако надо учесть следующее:
Из этого описания понятны основные функции системы, изображенные на рисунке с помощью нотации диаграммы прецедентов UML. На диаграмме не отображена роль администратора базы данных, так как администратор обычно взаимодействует с системой не через интерфейс, а через выполнение SQL-запросов.
Несмотря на то, что мы не будет разрабатывать интерфейс информационной системы и текстовые описания прецедентов, дальше нас будут интересовать данные, необходимые для выполнения того или иного прецедента, а для этого надо выделить и описать сущности. Иначе, невозможно определить «какие данные должен вводить менеджер при добавлении фильма». Основные сущности, данные которых потребуются во время работы, показаны на рисунке, при этом используется нотация диаграммы классов UML. Каждый прямоугольник соответствует одной сущности, внутри записаны поля и типы данных.
Каждая сущность, кроме hall_row содержит поле id, которое идентифицирует объект. У сущности hall_row поле id не нужно, так как в одном и том же зале кинотеатра (id_hall) не могут повторяться номера рядов (number).
Когда пользователь выберет зал и прокат — система должна отобразить заполненность зала, при этом надо отобразить конфигурацию зала с пометкой занятых и свободных мест. Под конфигурацией зала тут имеется ввиду, что разные залы имеют разный размер, а ряды зала могут иметь различное количество мест. Поэтому в базе данных зал (hall) составляется из рядов (hall_row), одним из параметров которых является вместимость (capacity).
2 Построение концептуальной модели
Выше были отображены основные сущности, но не отображены роли пользователей, хотя их тоже должна хранить система. Они показаны ниже на ER-диаграмме в нотации Чена [1].
На диаграмме выделены роли кассира и менеджера, а также основные отношения между сущностями. На диаграмме нет роли администратора, но его роль заключается в:
На диаграмме не отражена роль посетителя, так как:
На диаграмме проставлены кратности связей, например, видно, что один менеджер может добавить много (N) прокатов. В этой базе не оказалось связей типа N:M, сложных или рекурсивных связей — такие связи являются препятствиями в проектировании и решаются изменением ее структуры.
Для формирования схемы данных необходимо сначала дополнить ER-диаграмму реквизитами сущностей (уточнить ее) — результат приведен на рисунке.
В ходе анализа этой диаграммы были найдены несколько недочетов, допущенных при выделении сущностей системы:
Исправленная ER-диаграмма приведена ниже:
Таблица менеджеров и кассиров не объединены в таблицу Users так как вопросы разграничения прав доступа в различных СУБД решаются по-разному. Так, в MS SQL пользователи добавляются с помощью специальных запросов типа:
CREATE LOGIN Manager_Name WITH PASSWORD=’Some Passwrd’;
при этом вообще нет необходимости хранить информацию об их логинах и паролях в таблицах. Однако, вопросы разграничения доступа решаются позже — на этапе физического проектирования.
3 Физическое проектирование
ER-диаграмма отражает основные таблицы, связи и атрибуты, на ее основе можно построить модель БД. На ER-диаграммы нет стандарта, но есть ряд нотаций (Чена, IDEFIX, Мартина и т.п.) [2], но на модель предметной области не удалось найти ни стандарта, ни нотаций. Однако, в ходе построения такой диаграммы обязательно выделяются ключевые поля (внешние и внутренние), иногда — индексы и типы данных. Схема базы данных, приведенная на рисунке, выполнена с использованием открытого инструмента plantuml [3], при этом:
Создание скриптов для объектов в среде SQL Server Management Studio
В этом учебнике вы научитесь создавать скрипты Transact-SQL (T-SQL) для различных объектов, доступных в SQL Server Management Studio (SSMS). В нем приводятся примеры создания скриптов для следующих объектов:
Чтобы создать скрипт любого объекта в обозревателе объектов, щелкните его правой кнопкой мыши и выберите команду Создать скрипт объекта как. В этом учебнике будет показан процесс.
предварительные требования
Для работы с этим учебником требуется среда SQL Server Management Studio, доступ к серверу SQL Server и база данных AdventureWorks.
Инструкции по восстановлению баз данных в SSMS см. в статье Восстановление базы данных.
Создание скриптов для запросов в графическом пользовательском интерфейсе
Вы можете создать связанный код T-SQL для задачи в графическом пользовательском интерфейсе SSMS, чтобы выполнить его. Ниже приведены примеры выполнения этого действия при резервном копировании базы данных и сжатии журнала транзакций. Аналогичные инструкции применимы к любому действию, выполняемому в графическом пользовательском интерфейсе.
Создание скрипта T-SQL при резервном копировании базы данных
Подключитесь к серверу SQL Server.
Щелкните правой кнопкой мыши базу данных Adventureworks2016 и последовательно выберите пункты > Задачи > Резервное копирование:
Настройте резервное копирование требуемым образом. В этом учебнике оставлены все параметры по умолчанию. Все изменения, внесенные в этом окне, также отражаются в скрипте.
Выберите пункты Создать скрипт > Добавить скрипт в новое окно запроса:
Просмотрите код T-SQL в окне запроса.
Выберите команду Выполнить, чтобы выполнить запрос на резервное копирование базы данных с помощью T-SQL.
Создание скрипта T-SQL при сжатии журнала транзакций
Щелкните правой кнопкой мыши базу данных Adventureworks2016 и последовательно выберите пункты > Задачи > Сжать > Файлы:
Выберите Журнал в раскрывающемся списке Тип файла:
Выберите пункты Скрипт и Добавить скрипт в буфер обмена:
Откройте окно Новый запрос и вставьте скрипт. (Щелкните правой кнопкой мыши в окне. Выберите команду Вставить.)
Выберите команду Выполнить, чтобы выполнить запрос и сжать журнал транзакций.
Создание скриптов для баз данных
В следующем разделе показано, как создать скрипт для базы данных с помощью команды Создать скрипт как или Создать скрипты. Команда Создать скрипт как повторно создает базу данных и ее параметры конфигурации. Команда Создать скрипты позволит создать скрипт для схемы и данных. В этом разделе вы сможете создать две новых базы данных. Для создания AdventureWorks2016a используется команда Создать скрипт как. Для создания AdventureWorks2016b используется команда Создать скрипты.
Создание скрипта базы данных с помощью команды «Создать скрипт»
Подключитесь к серверу SQL Server.
Щелкните правой кнопкой мыши базу данных AdventureWorks2016 и последовательно выберите пункты > Создать скрипт базы данных как > Создать в > Новое окно редактора запросов:
Просмотрите запрос на создание базы данных в окне:
. Этот параметр создает скрипт только параметров конфигурации базы данных.
Нажмите клавиши CTRL+F, чтобы открыть диалоговое окно Найти. Щелкните стрелку вниз, чтобы открыть пункт Заменить. Вверху, в строке Найти, введите AdventureWorks2016, а внизу, в строке Заменить, введите AdventureWorks2016a.
Выберите Заменить все, чтобы заменить все значения AdventureWorks2016 на AdventureWorks2016a.
Выберите команду Выполнить, чтобы выполнить запрос и создать базу данных AdventureWorks2016a.
Создание скрипта базы данных с помощью команды «Создать скрипты»
Подключитесь к серверу SQL Server.
Щелкните правой кнопкой мыши AdventureWorks2016 и последовательно выберите пункты > Задачи > Создать скрипты:
Откроется страница Введение. Нажмите кнопку Далее, чтобы открыть страницу Выбор объектов. Вы можете выбрать всю базу данных или отдельные объекты в базе данных. Выберите Внести в скрипт всю базу данных целиком вместе со всеми объектами.
Нажмите кнопку Далее, чтобы открыть страницу Задание параметров скриптов. Здесь вы можете настроить расположение для сохранения скрипта и другие дополнительные параметры.
а. Выберите команду Сохранить в новом окне запроса.
b. Выберите пункт Дополнительно и убедитесь, что заданы следующие значения параметров:
Вы можете создать скрипт данных для базы данных при выборе варианта Схема и данные для параметра Типы данных для создания скриптов. Однако это не подходит для крупных баз данных. Может потребоваться больше памяти, чем SSMS может выделить. Это ограничение не влияет на небольшие базы данных. Если вы хотите переместить данные в более крупную базу данных, используйте мастер импорта и экспорта.
Нажмите кнопку ОК, а затем кнопку Далее.
Нажмите кнопку Далее на странице Сводка. После нажмите кнопку Далее еще раз, чтобы создать скрипт в окне Новый запрос.
Откройте диалоговое окно Найти (CTRL+F). Щелкните стрелку вниз, чтобы открыть пункт Заменить. Вверху, в строке Найти, введите AdventureWorks2016. Внизу, в строке Заменить, введите AdventureWorks2016b.
Выберите Заменить все, чтобы заменить все значения AdventureWorks2016 на AdventureWorks2016b.
Выберите команду Выполнить, чтобы выполнить запрос и создать базу данных AdventureWorks2016b.
Создание скриптов для таблиц
В этом разделе описывается, как создать скрипты для таблиц базы данных. С помощью этого параметра вы можете создать таблицу или удалить ее, а затем создать. Он также позволяет создать скрипт T-SQL, связанный с изменением таблицы: вставкой или обновлением. В этом разделе вы сможете удалить таблицу, а затем создать ее.
Подключитесь к серверу SQL Server.
Разверните узел Базы данных.
Разверните узел базы данных AdventureWorks2016.
Разверните узел Таблицы.
Щелкните правой кнопкой мыши dbo.ErrorLog и последовательно выберите пункты > Создать скрипт таблицы как > Удалить и создать в > Новое окно редактора запросов:
Выберите команду Выполнить, чтобы выполнить запрос. Это действие удаляет таблицу Errorlog и повторно создает ее.
Таблица Errorlog в базе данных AdventureWorks2016 по умолчанию пуста. Вы не потеряете данные, если удалите ее. Однако если в таблице есть данные, при выполнении этих действий они будут потеряны.
Создание скриптов для хранимых процедур
В этом разделе вы узнаете, как удалить и создать хранимую процедуру.
Подключитесь к серверу SQL Server.
Разверните узел Базы данных.
Разверните узел Программируемость.
Разверните узел Хранимые процедуры.
Щелкните правой кнопкой мыши хранимую процедуру dbo.uspGetBillOfMaterials и последовательно выберите пункты > Создать скрипт хранимой процедуры как > Удалить и создать в > Новое окно редактора запросов:
Создание скриптов для расширенных событий
В этом разделе описывается, как создать скрипты для расширенных событий.
Подключитесь к серверу SQL Server.
Разверните узел Управление.
Разверните узел Расширенные события.
Разверните узел Сеансы.
Щелкните правой кнопкой мыши нужный расширенный сеанс и выберите пункты Создать скрипт для сеанса > СОЗДАТЬ в > Новое окно редактора запросов:
В новом окне редактора запросов измените имя сеанса с system_health на system_health2. Выберите команду Выполнить, чтобы выполнить запрос.
Щелкните правой кнопкой мыши Сеансы в обозревателе объектов. Выберите команду Обновить, чтобы увидеть новый сеанс расширенного события. Зеленый значок рядом с сеансом означает, что сеанс выполняется. Красный значок указывает, что сеанс остановлен.
Вы можете запустить сеанс, щелкнув его правой кнопкой мыши и выбрав команду Запустить. Так как это копия уже запущенного сеанса system_health, этот шаг можно пропустить. Вы можете удалить копию расширенного сеанса события, щелкнув его правой кнопкой мыши и выбрав команду Удалить.
Дальнейшие действия
Лучший способ познакомиться с SSMS — это поработать в среде самостоятельно. Эти руководства и статьи помогут вам ознакомиться с различными функциями SSMS. С их помощью вы научитесь работать с компонентами SSMS и легко находить регулярно используемые функции.
Ведение разработки БД. Шаблоны создания/изменения объектов MSSQL
При постоянной работе с MSSQL необходимо создавать различные объекты БД: таблицы, представления, триггеры и т.д.
В статье приведу шаблоны SQL запросов, которые помогут, и, возможно, стандартизируют подход создания кода на языке T-SQL.
Кроме этого, опишу о том, как я веду репозиторий БД в системе контроля версий.
Основные требования реализации SQL скриптов
Далее приводятся примеры SQL файлов и BAT файл для запуска этих SQL запросов.
Шаблоны T-SQL
Ведение репозитория
1) Репозиторий состоит из папок, разделенных по типу объектов (например: Tables, Views, Triggers и т.д.)
2) Каждый объект БД хранится в отдельном SQL файле для удобства просмотра истории изменений в системе контроля версий.
3) Если изменения касаются данных, то такие изменения ведутся в отдельном файле «CommonChanges (version 000).sql», который создается на каждое обновление БД
4) Для автоматизации применения изменений на нескольких серверах запросы выполняются с помощью BAT файла.
Достоинства
1) Удобно отслеживать историю изменения каждого объекта БД
2) Не требуется разрабатывать собственные приложения для выполнения SQL файлов
3) Работает на всех версиях MSSQL и Windows
4) Настройки соединения с БД, имена БД и др. переменные хранятся в одном файле «Settings.bat», которые легко изменить
Недостатки
1) Не предусмотрен общий откат изменений, если какой-то скрипт выполнится с ошибкой
2) Если репозиторий состоит из большого количества объектов БД, то чтобы не передавать заказчику весь репозиторий, нужно копировать файлы (входящие в обновление) в отдельный каталог
Выгрузка структуры БД
В качестве автоматизации выгрузки структуры БД, в формате один объект = один файл, на данный момент использую SSMS, но в скором времени подобный функционал будет в моей программе ImportExportDataSql, которую рекомендую всем разработчикам БД.
Немного о ImportExportDataSql
Приложение ImportExportDataSql бесплатное, без рекламы, оповещает о новых версиях, наличие командной строки и Вы можете скачать его и использовать в своих проектах.
С помощью ImportExportDataSql Вы сможете:
Главной особенностью ImportExportDataSql, является то, что можно объединять несколько SELECT запросов, выгружая результат в виде SQL в один файл.
Добавляйтесь в группу VK, пишите свои пожелания, буду рад доработать приложение под Ваши нужды.
Заключение
Рад был поделиться результатом своего опыта. Данный способ ведения репозитория меня вполне устраивает. Надеюсь, эта статья поможет нам навести порядок в базах данных.