Меню

Создание скриптов для расширенных событий

Создание скриптов для объектов в среде 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.

Создание скрипта T-SQL при сжатии журнала транзакций

Щелкните правой кнопкой мыши базу данных Adventureworks2016 и последовательно выберите пункты > Задачи > Сжать > Файлы:

Сжатие файлов

Выберите Журнал в раскрывающемся списке Тип файла:

Сжатие журнала транзакций

Выберите пункты Скрипт и Добавить скрипт в буфер обмена:

Вывести скрипт в буфер обмена

Откройте окно Новый запрос и вставьте скрипт. (Щелкните правой кнопкой мыши в окне. Выберите команду Вставить.)

Вставка скрипта

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

Создание скриптов для баз данных

В следующем разделе показано, как создать скрипт для базы данных с помощью команды Создать скрипт как или Создать скрипты. Команда Создать скрипт как повторно создает базу данных и ее параметры конфигурации. Команда Создать скрипты позволит создать скрипт для схемы и данных. В этом разделе вы сможете создать две новых базы данных. Для создания AdventureWorks2016a используется команда Создать скрипт как. Для создания AdventureWorks2016b используется команда Создать скрипты.

Создание скрипта базы данных с помощью команды «Создать скрипт»

Подключитесь к серверу SQL Server.

Разверните узел Базы данных .

Щелкните правой кнопкой мыши базу данных AdventureWorks2016 и последовательно выберите пункты > Создать скрипт базы данных как > Создать в > Новое окно редактора запросов:

Создание скрипта базы данных

Просмотрите запрос на создание базы данных в окне:

Созданный скрипт для базы данных. Этот параметр создает скрипт только параметров конфигурации базы данных.

Нажмите клавиши CTRL+F, чтобы открыть диалоговое окно Найти. Щелкните стрелку вниз, чтобы открыть пункт Заменить. Вверху, в строке Найти, введите AdventureWorks2016, а внизу, в строке Заменить, введите AdventureWorks2016a.

Выберите Заменить все, чтобы заменить все значения AdventureWorks2016 на AdventureWorks2016a.

Поиск и замена

Выберите команду Выполнить, чтобы выполнить запрос и создать базу данных AdventureWorks2016a.

Создание скрипта базы данных с помощью команды «Создать скрипты»

Подключитесь к серверу SQL Server.

Разверните узел Базы данных .

Щелкните правой кнопкой мыши AdventureWorks2016 и последовательно выберите пункты > Задачи > Создать скрипты:

Создание скриптов для баз данных

Откроется страница Введение. Нажмите кнопку Далее, чтобы открыть страницу Выбор объектов. Вы можете выбрать всю базу данных или отдельные объекты в базе данных. Выберите Внести в скрипт всю базу данных целиком вместе со всеми объектами.

Создание скриптов для объектов

Нажмите кнопку Далее, чтобы открыть страницу Задание параметров скриптов. Здесь вы можете настроить расположение для сохранения скрипта и другие дополнительные параметры.

а. Выберите команду Сохранить в новом окне запроса.

b. Выберите пункт Дополнительно и убедитесь, что заданы следующие значения параметров:

  • параметр Статистика скриптов имеет значение Статистика скриптов;
  • параметр Типы данных для создания скриптов имеет значение Только схема;
  • параметр Индексы скриптов имеет значение True.

Объекты скриптов

Вы можете создать скрипт данных для базы данных при выборе варианта Схема и данные для параметра Типы данных для создания скриптов. Однако это не подходит для крупных баз данных. Может потребоваться больше памяти, чем SSMS может выделить. Это ограничение не влияет на небольшие базы данных. Если вы хотите переместить данные в более крупную базу данных, используйте мастер импорта и экспорта.

Нажмите кнопку ОК, а затем кнопку Далее.

Нажмите кнопку Далее на странице Сводка. После нажмите кнопку Далее еще раз, чтобы создать скрипт в окне Новый запрос.

Откройте диалоговое окно Найти (CTRL+F). Щелкните стрелку вниз, чтобы открыть пункт Заменить. Вверху, в строке Найти, введите AdventureWorks2016. Внизу, в строке Заменить, введите AdventureWorks2016b.

Выберите Заменить все, чтобы заменить все значения AdventureWorks2016 на AdventureWorks2016b.

AdventureWorks 2016

Выберите команду Выполнить, чтобы выполнить запрос и создать базу данных 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 и легко находить регулярно используемые функции.

Источник

Как сгенерировать SQL скрипт создания объектов и данных в Microsoft SQL Server?

Привет! Сегодня мы поговорим о том, как можно сгенерировать SQL скрипты создания объектов базы данных Microsoft SQL Server, включая сами данные, стандартными средствами SQL Server Management Studio (SSMS).

SQL скрипты создания объектов базы данных Microsoft SQL Server

  1. Что такое SQL скрипт объекта базы данных?
  2. Что могут содержать SQL скрипты?
  3. Для чего могут потребоваться SQL скрипты объектов базы данных?
  4. Как создать SQL скрипт объекта базы данных в Microsoft SQL Server?
  5. Создание SQL скрипта объекта базы данных Microsoft SQL Server
  6. Шаг 1 – Запускаем SSMS
  7. Шаг 2 – Запускаем задачу «Сформировать скрипты»
  8. Шаг 3 – Выбираем объекты для включения в SQL скрипт
  9. Шаг 4 – Задание параметров SQL скрипта
  10. Шаг 5 – Проверка параметров и запуск процесса создания скрипта
  11. Шаг 6 – Завершение процесса и результат
  12. Видео-инструкция
Читайте также:  Таблица владимиро суздальское княжество новгородская республика южное княжество

Что такое SQL скрипт объекта базы данных?

SQL скрипт объекта базы данных – это SQL инструкция, с помощью которой создается этот объект, сохраненная в текстовом файле.

Иными словами, это простой SQL запрос, обычно сохраненный в текстовом файле с расширением .sql. В этом SQL запросе содержатся все необходимые инструкции создания объекта (или объектов), включая инструкции наполнения его данными.

Такой SQL скрипт можно открыть любым текстовым редактором, скопировать текст SQL запроса и выполнить, например, в среде SQL Server Management Studio, таким образом, создав объект базы данных, не разрабатывая соответствующие SQL инструкции самостоятельно.

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

Что могут содержать SQL скрипты?

SQL скрипты объектов базы данных могут содержать:

  • Инструкции создания таблиц (CREATE);
  • Заполнение таблиц (инструкции INSERT);
  • Определение представлений, функций, хранимых процедур, триггеров;
  • Определение ограничений и индексов;
  • Определение создания других объектов;
  • И другие 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 – Запускаем задачу «Сформировать скрипты»

Далее в обозревателе объектов находим нужную нам базу данных, затем открываем контекстное меню правой кнопкой мыши, ищем пункт «Задачи», и выбираем задачу «Сформировать скрипты».

Скриншот 1

В итоге запустится мастер создания скриптов. В окне «Введение» можем сразу нажать «Далее».

Скриншот 2

Шаг 3 – Выбираем объекты для включения в SQL скрипт

После этого нам нужно выбрать объекты базы данных, которые необходимо включить в SQL скрипт. При этом нам доступно два варианта:

  • Создать скрипт для всей базы данных и всех ее объектов – этот вариант предполагает, что Вам нужен скрипт создания всех объектов в БД;
  • Выбрать отдельные объекты базы данных – в данном случае в скрипт включатся SQL инструкции только тех объектов, которые Вы укажете.

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

Скриншот 3

Шаг 4 – Задание параметров SQL скрипта

Теперь мы можем указать параметры формирования скрипта, в частности, каким образом мы хотим получить скрипт.

Доступно 3 способа:

  • Сохранить в файл – при этом мы можем сами указать нужный нам каталог для сохранения;
  • Сохранить в буфер обмена;
  • Сохранить в новое окно запросов.

Также есть и дополнительные параметры, чтобы их открыть, необходимо нажать на соответствующую кнопку.

Скриншот 4

В дополнительных параметрах мы можем более тонко настроить принцип формирования SQL скрипта, например, для того чтобы скрипт включал еще и SQL инструкции создания данных, необходимо в параметрах отметить соответствующий пункт, т.е. указать «Схема и данные».

Также Вы можете включить в скрипты инструкции DROP на случай, если Вам нужно пересоздать объекты.

После того как все параметры заданы, нажимаем «ОК», а после для продолжения кнопку «Далее».

Скриншот 5

Шаг 5 – Проверка параметров и запуск процесса создания скрипта

На данном шаге все проверяем и запускаем сам процесс формирования скрипта, т.е. нажимаем «Далее».

Скриншот 6

Шаг 6 – Завершение процесса и результат

Когда процесс будет завершен, программа сообщит Вам об этом, нажимаем «Готово».

Скриншот 7

В результате в каталоге, который Вы указали в параметрах формирования скрипта, появится SQL скрипт, а по факту обычный текстовый файл с расширением .sql.

Если его открыть, например, текстовым редактором, то можно посмотреть на все SQL инструкции. Они будут выглядеть примерно следующим образом.

Скриншот 8

Видео-инструкция

На сегодня это все, надеюсь, материал был Вам полезен, пока!

Источник



Ms sql скрипты заполнение таблицы

надо получить такой текст:

insert into Customer (ID, Name) values (1, Alex);
insert into Customer (ID, Name) values (2, John);
insert into Customer (ID, Name) values (3, NULL);

и так для любой таблицы

есть какие нибудь соображения по этому поводу?
первую часть(имена полей) я из метаданных выбираю, а вот со значениями происходит затык.

Posted via ActualForum NNTP Server 1.3

Posted via ActualForum NNTP Server 1.3

хехе
кому надо, допишет пару строк
и UDT там уже прикручено

Declare @TableName varchar(64),
@StrColumns varchar(max),
@StrValues varchar(max),
@StrNull char(4),
@SqlStr nvarchar(max),
@return_value int

Set @SqlStr = ‘SELECT ‘
Set @TableName = ‘Customers’
Set @StrColumns = »
Set @StrValues = »
Set @StrNull = ‘NULL’

SELECT @StrColumns = @StrColumns + COLUMN_NAME + ‘ ,’ FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND COLUMN_NAME NOT LIKE ‘%Timestamp%’
ORDER BY ORDINAL_POSITION

SET @StrColumns = SUBSTRING(@StrColumns, 1, Len(@StrColumns)-1)

SELECT @StrValues = @StrValues +
‘ISNULL(‘ + ‘CHAR(39) + ‘ + ‘CAST(‘ + COLUMN_NAME + ‘ AS VARCHAR(max))’ + ‘ + CHAR(39) ‘ + ‘,’ + CHAR(39) +’NULL’ + CHAR(39) +’)’ + ‘ + ‘ + CHAR(39) + ‘,’ + CHAR(39) + ‘ + ‘
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND COLUMN_NAME NOT LIKE ‘%Timestamp%’
ORDER BY ORDINAL_POSITION

SET @StrValues = SUBSTRING(@StrValues, 1, Len(@StrValues)-6)

Источник

Изменение, заполнение и обновление таблиц

Дата изменения: 12.10.2017

После создания пустых таблиц следующим логическим шагом является заполнение их данными и обновление этих данных. Для этого в Transact-SQL предназначена пара инструкций INSERT – UPDATE.

Однако, часто на позднем этапе проектирования возникает необходимость изменить саму схему таблиц. Например, если изменился первичный ключ или тип данных столбца. Чтобы не удалять старые таблицы и не создавать их заново с помочью CREATE TABLE c правильными параметрами, применяется инструкция ALTER TABLE. Применение этих трех конструкций рассматривается ниже.

Заполнение таблиц

Заполнить таблицу данными можно через конструкцию CREATE TABLE, однако более эффективным подходом является разделять создание таблицы и ее заполнение, особенно новичкам в SQL, потому что:

  • визуально понятнее;
  • удобнее, если наполнение таблиц поэтапное.

Чтобы получить следующий вид таблицы:

Потребуется создать ее с помощью CREATE TABLE и заполнить, применив инструкцию INSERT. Следующая инструкция добавляет одну строку в уже созданную нами таблицу housemates:

В примере выше следует различать два блока конструкции INSERT:

INTO – указывающий на таблицу в которую добавляются данные

VALUES – инициализирующий построчный ввод.

В параметрах (в скобках) блока INTO перечисляются столбцы в порядке их следования. Допустима запись без параметров, если порядок следования столбцов при создании таблицы и заполнении ее совпадают. Следующий код идентичен предыдушему:

Если необходимо поменять порядок заполнения, то это нужно явно указать:

В блоке VALUES производится построчная инициализация в порядке следования столбцов блока INTO. Заполнение строки – это перечисление значений ячеек в скобках. Значения перечисляются через запятую, строки между собой тоже.

Читайте также:  Таблица строение почек строение нефрона

Применить конструкцию INSERT можно только к тем столбцам в которых еще нет данных. Иначе возникнет ошибка выполнения запроса.

Обновление таблицы

Потребность в обновлении таблиц возникает если необходимо изменить уже введенные в таблицу данные. Выполняется с помощью инструкции UPDATE. Если INSERT работает только с пустыми таблицами, то UPDATE только с заполненными. Если запрос будет адресован к незаполненным ячейкам, возникнет ошибка.

Следующий код присвоит новый почтовый ящик жителю дома с идентификационным номером 103.

Блок SET – это блок изменений. Если нужно обновить значение нескольких ячеек, то они перечисляются через запятую.

Изменение таблицы

На поздних этапах проектирования или уже после разработки базы часто возникает необходимость:

  • добавить или удалить столбец;
  • изменить тип столбца;
  • назначить столбец или группу столбцов первичным или внешним ключом, или снять эти ограничения.

Для всех этих операций предназначена инструкция переопределения ATER TABLE.

Чтобы добавить столбец инструкция ALTER TABLE применяется с предложением ADD. Добавим новый столбец к таблице housemates из прошлого раздела:

Нужно применить к нему предложение ALTER COLUMN внутри ALTER TABLE:

Удаляется столбец применением DROP COLUMN внутри ALTER TABLE:

Первичный или внешний ключ удаляется и добавляется конструкциями ALTER TABLE ADD CONSTRAINT/DROP CONSTRAINT, соответственно:

Если попытаться присвоить таблице первичный ключ, который уже там есть транслятор запросов вернет ошибку. Поэтому если в таблице уже есть первичный ключ, следует снять его и назначить группу столбцов первичным ключом. Например, так:

Превью Изменение, заполнение и обновление таблиц2017-10-12 2017-10-12 Изменение, заполнение и обновление таблиц Александр Сажин

Источник

Создание INSERT скрипта для данных таблицы MSSQL.

Часто возникает потребность создать скрипт для заполнения таблицы данными, которые уже есть в таблице 🙂
Ну, скажем, данные таблицы были заполнены уже давно, каким либо способом. Бэкап или экспорт делать не хочется, а нужен простой текстовый скрипт с INSERT выражениями. К сожалению, ни утилиты командной строки, ни Management Studio это делать не умеет. А есть только программы сторонних производителей, которые стоят денег.
Что делать, есть покупка нецелесообразна, а в ручную ковыряться не хочется?

В сети нашел полезную хранимку. Следующий скрипт взят с сайта его производителя.

PRINT ‘Using Master database’
USE master
GO

PRINT ‘Checking for the existence of this procedure’
IF ( SELECT OBJECT_ID ( ‘sp_generate_inserts’ , ‘P’ ) ) IS NOT NULL —means, the procedure already exists
BEGIN
PRINT ‘Procedure already exists. So, dropping it’
DROP PROC sp_generate_inserts
END
GO

CREATE PROC sp_generate_inserts
(
@table_name varchar ( 776 ) , — The table/view for which the INSERT statements will be generated using the existing data
@target_table varchar ( 776 ) = NULL , — Use this parameter to specify a different table name into which the data will be inserted
@include_column_list bit = 1 , — Use this parameter to include/ommit column list in the generated INSERT statement
@ FROM varchar ( 800 ) = NULL , — Use this parameter to filter the rows based on a filter condition (using WHERE)
@include_timestamp bit = 0 , — Specify 1 for this parameter, if you want to include the TIMESTAMP/ROWVERSION column’s data in the INSERT statement
@debug_mode bit = 0 , — If @debug_mode is set to 1, the SQL statements constructed by this procedure will be printed for later examination
@owner varchar ( 64 ) = NULL , — Use this parameter if you are not the owner of the table
@ommit_images bit = 0 , — Use this parameter to generate INSERT statements by omitting the ‘image’ columns
@ommit_identity bit = 0 , — Use this parameter to ommit the identity columns
@top int = NULL , — Use this parameter to generate INSERT statements only for the TOP n rows
@cols_to_include varchar ( 8000 ) = NULL , — List of columns to be included in the INSERT statement
@cols_to_exclude varchar ( 8000 ) = NULL , — List of columns to be excluded from the INSERT statement
@disable_constraints bit = 0 , — When 1, disables foreign key constraints and enables them after the INSERT statements
@ommit_computed_cols bit = 0 — When 1, computed columns will not be included in the INSERT statement

Purpose: To generate INSERT statements from existing data.
These INSERTS can be executed to regenerate the data at some other location.
This procedure is also useful to create a database setup, where in you can
script your data along with your table definitions.

Written by: Narayana Vyas Kondreddi
http://vyaskn.tripod.com

Acknowledgements:
Divya Kalra — For beta testing
Mark Charsley — For reporting a problem with scripting uniqueidentifier columns with NULL values
Artur Zeygman — For helping me simplify a bit of code for handling non-dbo owned tables
Joris Laperre — For reporting a regression bug in handling text/ntext columns

Tested on: SQL Server 7.0 and SQL Server 2000 and SQL Server 2005

Date created: January 17th 2001 21:52 GMT

Date modified: May 1st 2002 19:50 GMT

NOTE: This procedure may not work with tables with too many columns.
Results can be unpredictable with huge text columns or SQL Server 2000’s sql_variant data types
Whenever possible, Use @include_column_list parameter to ommit column list in the INSERT statement, for better results
IMPORTANT: This procedure is not tested with internation data (Extended characters or Unicode). If needed
you might want to convert the datatypes of character variables in this procedure to their respective unicode counterparts
like nchar and nvarchar

ALSO NOTE THAT THIS PROCEDURE IS NOT UPDATED TO WORK WITH NEW DATA TYPES INTRODUCED IN SQL SERVER 2005 / YUKON

Example 1: To generate INSERT statements for table ‘titles’:

EXEC sp_generate_inserts ‘titles’

Example 2: To ommit the column list in the INSERT statement: (Column list is included by default)
IMPORTANT: If you have too many columns, you are advised to ommit column list, as shown below,
to avoid erroneous results

EXEC sp_generate_inserts ‘titles’, @include_column_list = 0

Example 3: To generate INSERT statements for ‘titlesCopy’ table from ‘titles’ table:

EXEC sp_generate_inserts ‘titles’, ‘titlesCopy’

Example 4: To generate INSERT statements for ‘titles’ table for only those titles
which contain the word ‘Computer’ in them:
NOTE: Do not complicate the FROM or WHERE clause here. It’s assumed that you are good with T-SQL if you are using this parameter

EXEC sp_generate_inserts ‘titles’, @from = «from titles where title like ‘%Computer%’»

Example 5: To specify that you want to include TIMESTAMP column’s data as well in the INSERT statement:
(By default TIMESTAMP column’s data is not scripted)

EXEC sp_generate_inserts ‘titles’, @include_timestamp = 1

Example 6: To print the debug information:

EXEC sp_generate_inserts ‘titles’, @debug_mode = 1

Example 7: If you are not the owner of the table, use @owner parameter to specify the owner name
To use this option, you must have SELECT permissions on that table

EXEC sp_generate_inserts Nickstable, @owner = ‘Nick’

Example 8: To generate INSERT statements for the rest of the columns excluding images
When using this otion, DO NOT set @include_column_list parameter to 0.

EXEC sp_generate_inserts imgtable, @ommit_images = 1

Example 9: To generate INSERT statements excluding (ommiting) IDENTITY columns:
(By default IDENTITY columns are included in the INSERT statement)

EXEC sp_generate_inserts mytable, @ommit_identity = 1

Example 10: To generate INSERT statements for the TOP 10 rows in the table:

EXEC sp_generate_inserts mytable, @top = 10

Example 11: To generate INSERT statements with only those columns you want:

EXEC sp_generate_inserts titles, @cols_to_include = «‘title’,’title_id’,’au_id’»

Example 12: To generate INSERT statements by omitting certain columns:

EXEC sp_generate_inserts titles, @cols_to_exclude = «‘title’,’title_id’,’au_id’»

Example 13: To avoid checking the foreign key constraints while loading data with INSERT statements:

EXEC sp_generate_inserts titles, @disable_constraints = 1

Example 14: To exclude computed columns from the INSERT statement:
EXEC sp_generate_inserts MyTable, @ommit_computed_cols = 1
***********************************************************************************************************/

—Making sure user only uses either @cols_to_include or @cols_to_exclude
IF ( ( @cols_to_include IS NOT NULL ) AND ( @cols_to_exclude IS NOT NULL ) )
BEGIN
RAISERROR ( ‘Use either @cols_to_include or @cols_to_exclude. Do not use both the parameters at once’ , 16 , 1 )
RETURN — 1 —Failure. Reason: Both @cols_to_include and @cols_to_exclude parameters are specified
END

—Making sure the @cols_to_include and @cols_to_exclude parameters are receiving values in proper format
IF ( ( @cols_to_include IS NOT NULL ) AND ( PATINDEX ( » ‘%’ » ,@cols_to_include ) = 0 ) )
BEGIN
RAISERROR ( ‘Invalid use of @cols_to_include property’ , 16 , 1 )
PRINT ‘Specify column names surrounded by single quotes and separated by commas’
PRINT ‘Eg: EXEC sp_generate_inserts titles, @cols_to_include = «‘ ‘title_id’ ‘,’ ‘title’ ‘»‘
RETURN — 1 —Failure. Reason: Invalid use of @cols_to_include property
END

Читайте также:  Как создать таблицу график дежурства

IF ( ( @cols_to_exclude IS NOT NULL ) AND ( PATINDEX ( » ‘%’ » ,@cols_to_exclude ) = 0 ) )
BEGIN
RAISERROR ( ‘Invalid use of @cols_to_exclude property’ , 16 , 1 )
PRINT ‘Specify column names surrounded by single quotes and separated by commas’
PRINT ‘Eg: EXEC sp_generate_inserts titles, @cols_to_exclude = «‘ ‘title_id’ ‘,’ ‘title’ ‘»‘
RETURN — 1 —Failure. Reason: Invalid use of @cols_to_exclude property
END

—Checking to see if the database name is specified along wih the table name
—Your database context should be local to the table for which you want to generate INSERT statements
—specifying the database name is not allowed
IF ( PARSENAME ( @table_name, 3 ) ) IS NOT NULL
BEGIN
RAISERROR ( ‘Do not specify the database name. Be in the required database and just specify the table name.’ , 16 , 1 )
RETURN — 1 —Failure. Reason: Database name is specified along with the table name, which is not allowed
END

—Checking for the existence of ‘user table’ or ‘view’
—This procedure is not written to work on system tables
—To script the data in system tables, just create a view on the system tables and script the view instead

IF @owner IS NULL
BEGIN
IF ( ( OBJECT_ID ( @table_name, ‘U’ ) IS NULL ) AND ( OBJECT_ID ( @table_name, ‘V’ ) IS NULL ) )
BEGIN
RAISERROR ( ‘User table or view not found.’ , 16 , 1 )
PRINT ‘You may see this error, if you are not the owner of this table or view. In that case use @owner parameter to specify the owner name.’
PRINT ‘Make sure you have SELECT permission on that table or view.’
RETURN — 1 —Failure. Reason: There is no user table or view with this name
END
END
ELSE
BEGIN
IF NOT EXISTS ( SELECT 1 FROM INFORMATION_SCHEMA. TABLES WHERE TABLE_NAME = @table_name AND ( TABLE_TYPE = ‘BASE TABLE’ OR TABLE_TYPE = ‘VIEW’ ) AND TABLE_SCHEMA = @owner )
BEGIN
RAISERROR ( ‘User table or view not found.’ , 16 , 1 )
PRINT ‘You may see this error, if you are not the owner of this table. In that case use @owner parameter to specify the owner name.’
PRINT ‘Make sure you have SELECT permission on that table or view.’
RETURN — 1 —Failure. Reason: There is no user table or view with this name
END
END

—Variable declarations
DECLARE @Column_ID int,
@Column_List varchar ( 8000 ) ,
@Column_Name varchar ( 128 ) ,
@Start_Insert varchar ( 786 ) ,
@Data_Type varchar ( 128 ) ,
@Actual_Values varchar ( 8000 ) , —This is the string that will be finally executed to generate INSERT statements
@IDN varchar ( 128 ) —Will contain the IDENTITY column’s name in the table

—Variable Initialization
SET @IDN = »
SET @Column_ID = 0
SET @Column_Name = »
SET @Column_List = »
SET @Actual_Values = »

IF @owner IS NULL
BEGIN
SET @Start_Insert = ‘INSERT INTO ‘ + ‘[‘ + RTRIM ( COALESCE ( @target_table,@table_name ) ) + ‘]’
END
ELSE
BEGIN
SET @Start_Insert = ‘INSERT ‘ + ‘[‘ + LTRIM ( RTRIM ( @owner ) ) + ‘].’ + ‘[‘ + RTRIM ( COALESCE ( @target_table,@table_name ) ) + ‘]’
END

—To get the first column’s ID

SELECT @Column_ID = MIN ( ORDINAL_POSITION )
FROM INFORMATION_SCHEMA. COLUMNS ( NOLOCK )
WHERE TABLE_NAME = @table_name AND
( @owner IS NULL OR TABLE_SCHEMA = @owner )

—Loop through all the columns of the table, to get the column names and their data types
WHILE @Column_ID IS NOT NULL
BEGIN
SELECT @Column_Name = QUOTENAME ( COLUMN_NAME ) ,
@Data_Type = DATA_TYPE
FROM INFORMATION_SCHEMA. COLUMNS ( NOLOCK )
WHERE ORDINAL_POSITION = @Column_ID AND
TABLE_NAME = @table_name AND
( @owner IS NULL OR TABLE_SCHEMA = @owner )

IF @cols_to_include IS NOT NULL —Selecting only user specified columns
BEGIN
IF CHARINDEX ( » » + SUBSTRING ( @Column_Name, 2 ,LEN ( @Column_Name ) — 2 ) + » » ,@cols_to_include ) = 0
BEGIN
GOTO SKIP_LOOP
END
END

IF @cols_to_exclude IS NOT NULL —Selecting only user specified columns
BEGIN
IF CHARINDEX ( » » + SUBSTRING ( @Column_Name, 2 ,LEN ( @Column_Name ) — 2 ) + » » ,@cols_to_exclude ) <> 0
BEGIN
GOTO SKIP_LOOP
END
END

—Making sure to output SET IDENTITY_INSERT ON/OFF in case the table has an IDENTITY column
IF ( SELECT COLUMNPROPERTY ( OBJECT_ID ( QUOTENAME ( COALESCE ( @owner,USER_NAME ( ) ) ) + ‘.’ + @table_name ) ,SUBSTRING ( @Column_Name, 2 ,LEN ( @Column_Name ) — 2 ) , ‘IsIdentity’ ) ) = 1
BEGIN
IF @ommit_identity = 0 —Determing whether to include or exclude the IDENTITY column
SET @IDN = @Column_Name
ELSE
GOTO SKIP_LOOP
END

—Making sure whether to output computed columns or not
IF @ommit_computed_cols = 1
BEGIN
IF ( SELECT COLUMNPROPERTY ( OBJECT_ID ( QUOTENAME ( COALESCE ( @owner,USER_NAME ( ) ) ) + ‘.’ + @table_name ) ,SUBSTRING ( @Column_Name, 2 ,LEN ( @Column_Name ) — 2 ) , ‘IsComputed’ ) ) = 1
BEGIN
GOTO SKIP_LOOP
END
END

—Tables with columns of IMAGE data type are not supported for obvious reasons
IF ( @Data_Type IN ( ‘image’ ) )
BEGIN
IF ( @ommit_images = 0 )
BEGIN
RAISERROR ( ‘Tables with image columns are not supported.’ , 16 , 1 )
PRINT ‘Use @ommit_images = 1 parameter to generate INSERTs for the rest of the columns.’
PRINT ‘DO NOT ommit Column List in the INSERT statements. If you ommit column list using @include_column_list=0, the generated INSERTs will fail.’
RETURN — 1 —Failure. Reason: There is a column with image data type
END
ELSE
BEGIN
GOTO SKIP_LOOP
END
END

—Determining the data type of the column and depending on the data type, the VALUES part of
—the INSERT statement is generated. Care is taken to handle columns with NULL values. Also
—making sure, not to lose any data from flot, real, money, smallmomey, datetime columns
SET @Actual_Values = @Actual_Values +
CASE
WHEN @Data_Type IN ( ‘char’ , ‘varchar’ , ‘nchar’ , ‘nvarchar’ )
THEN
‘COALESCE(‘ » » » ‘ + REPLACE(RTRIM(‘ + @Column_Name + ‘),’ » » » ‘,’ » » » » » ‘)+’ » » » ‘,’ ‘NULL’ ‘)’
WHEN @Data_Type IN ( ‘datetime’ , ‘smalldatetime’ )
THEN
‘COALESCE(‘ » » » ‘ + RTRIM(CONVERT(char,’ + @Column_Name + ‘,109))+’ » » » ‘,’ ‘NULL’ ‘)’
WHEN @Data_Type IN ( ‘uniqueidentifier’ )
THEN
‘COALESCE(‘ » » » ‘ + REPLACE(CONVERT(char(255),RTRIM(‘ + @Column_Name + ‘)),’ » » » ‘,’ » » » » » ‘)+’ » » » ‘,’ ‘NULL’ ‘)’
WHEN @Data_Type IN ( ‘text’ , ‘ntext’ )
THEN
‘COALESCE(‘ » » » ‘ + REPLACE(CONVERT(char(8000),’ + @Column_Name + ‘),’ » » » ‘,’ » » » » » ‘)+’ » » » ‘,’ ‘NULL’ ‘)’
WHEN @Data_Type IN ( ‘binary’ , ‘varbinary’ )
THEN
‘COALESCE(RTRIM(CONVERT(char,’ + ‘CONVERT(int,’ + @Column_Name + ‘))),’ ‘NULL’ ‘)’
WHEN @Data_Type IN ( ‘timestamp’ , ‘rowversion’ )
THEN
CASE
WHEN @include_timestamp = 0
THEN
» ‘DEFAULT’ »
ELSE
‘COALESCE(RTRIM(CONVERT(char,’ + ‘CONVERT(int,’ + @Column_Name + ‘))),’ ‘NULL’ ‘)’
END
WHEN @Data_Type IN ( ‘float’ , ‘real’ , ‘money’ , ‘smallmoney’ )
THEN
‘COALESCE(LTRIM(RTRIM(‘ + ‘CONVERT(char, ‘ + @Column_Name + ‘,2)’ + ‘)),’ ‘NULL’ ‘)’
ELSE
‘COALESCE(LTRIM(RTRIM(‘ + ‘CONVERT(char, ‘ + @Column_Name + ‘)’ + ‘)),’ ‘NULL’ ‘)’
END + ‘+’ + » ‘,’ » + ‘ + ‘

—Generating the column list for the INSERT statement
SET @Column_List = @Column_List + @Column_Name + ‘,’

SKIP_LOOP: —The label used in GOTO

SELECT @Column_ID = MIN ( ORDINAL_POSITION )
FROM INFORMATION_SCHEMA. COLUMNS ( NOLOCK )
WHERE TABLE_NAME = @table_name AND
ORDINAL_POSITION > @Column_ID AND
( @owner IS NULL OR TABLE_SCHEMA = @owner )

—Loop ends here!
END

—To get rid of the extra characters that got concatenated during the last run through the loop
SET @Column_List = LEFT ( @Column_List,len ( @Column_List ) — 1 )
SET @Actual_Values = LEFT ( @Actual_Values,len ( @Actual_Values ) — 6 )

IF LTRIM ( @Column_List ) = »
BEGIN
RAISERROR ( ‘No columns to select. There should at least be one column to generate the output’ , 16 , 1 )
RETURN — 1 —Failure. Reason: Looks like all the columns are ommitted using the @cols_to_exclude parameter
END

—Forming the final string that will be executed, to output the INSERT statements
IF ( @include_column_list <> 0 )
BEGIN
SET @Actual_Values =
‘SELECT ‘ +
CASE WHEN @top IS NULL OR @top 0 THEN » ELSE ‘ TOP ‘ + LTRIM ( STR ( @top ) ) + ‘ ‘ END +
» » + RTRIM ( @Start_Insert ) +
‘ ‘ ‘+’ + » ‘(‘ + RTRIM ( @Column_List ) + » ‘+’ + » ‘)’ » +
‘ +’ ‘VALUES(‘ ‘+ ‘ + @Actual_Values + ‘+’ ‘)’ » + ‘ ‘ +
COALESCE ( @ FROM , ‘ FROM ‘ + CASE WHEN @owner IS NULL THEN » ELSE ‘[‘ + LTRIM ( RTRIM ( @owner ) ) + ‘].’ END + ‘[‘ + rtrim ( @table_name ) + ‘]’

Источник