SQL-Ex blog

Решение

В этом руководстве мы обсудим простой вариант для удаления всех 24 объектов SQL Server с помощью всего лишь трех операторов SQL. Обычно приходится писать оператор DROP для каждого объекта; однако вы можете удалить все ваши таблицы в одном операторе SQL, все представления в другом операторе SQL и все ваши хранимые процедуры в еще одном.

DROP Table для всех таблиц в базе данных SQL Server

Решение простое - перечислите все таблицы в вашей базе данных, например, в одном операторе DROP, разделив их запятыми.

Синтаксис примера:

DROP TABLE table1, table2, table3, table4, table5;

В нашем сценарии мы создадим 5 таблиц, заполним их сгенерированными данными, а затем удалим все пять таблиц с помощью одной команды SQL.

Для простоты все пять таблиц будут идентичны за исключением имен. Сначала давайте создадим начальную таблицу и наполним ее данными.

CREATE TABLE Students1(

     colID INT

   , name VARCHAR(20)

   , subject VARCHAR(20)

   );

GO

INSERT INTO Students1(name, subject)

VALUES ('Student1', 'Science')

     , ('Student2', 'Science')

     , ('Student3', 'History');

GO

Теперь создадим еще четыре таблицы, используя процесс "копирования таблицы".

SELECT *

INTO Students2

FROM Students1;

GO

SELECT *

INTO Students3

FROM Students2;

GO

SELECT *

INTO Students4

FROM Students3;

GO

SELECT *

INTO Students5

FROM Students4;

GO

Вы можете удалить все эти таблицы сразу с помощью одного запроса SQL.

DROP TABLE

Students1,

Students2,

Students3,

Students4,

Students5;

GO

Преимущества и недостатки

Как многое в SQL и в жизни, часто преимущества сопровождаются недостатками. Это также справедливо и для операторов массового удаления. Чтобы вы могли определить, в каком сценарии это даст выгоду, ниже приводится краткая сводка преимуществ и недостатков этой возможности.

Преимущества

• Вариант массового удаления совместим со всеми версиями SQL Server, включая Azure.


• Вы можете сэкономить время за счет сокращения кода.


• Если один из объектов в списке не существует или не может быть удален из-за зависимостей, это не влияет на остальные объекты в операторе DROP. Все остальные объекты в списке будут удалены.

Давайте рассмотрим последний пункт в действии.

Вот скриншот наших таблиц, находящихся сейчас в тестовой базе данных. Если вы уже удалили их, повторите выполнение скриптов CREATE TABLE.



Теперь давайте выполним оператор массового удаления всех таблиц, но допустим ошибку в имени одной из таблиц, чтобы смоделировать отсутствующую таблицу или таблицу со связью. Изменим имя таблицы Students3 на Students33 и выполним скрипт.

DROP TABLE

Students1,

Students2,

Students33,

Students4,

Students5;

GO

Вы должны получить похожее сообщение об ошибке:



Обратите внимание, что тут ничего не сказано о других таблицах, а только о Students33, которой не существует. Поэтому, когда мы обновим таблицы в браузере объектов, все таблицы кроме Students3 исчезнут.

Недостатки

• Вы не можете использовать IF EXISTS в операторе массового удаления.


• Вы не можете удалить одновременно объекты разных типов. Другими словами, вы должны удалить одной командой все таблицы, другой - все представления и т.д.

Нахождение имен объектов SQL

Итак, вы можете удалить все таблицы, представления, процедуры и т.д. с помощью одной команды. Нужно ли вам все еще печатать имена всех этих таблиц вручную? Как бы сэкономить время, особенно если некоторые объекты имеют длинные имена?

Давайте проясним этот вопрос, используя созданные ранее тестовые таблицы. Мы можем использовать некоторые инструменты, встроенные в SQL Server Management Studio (SSMS): 1) "sys.objects" и 2) возможность вернуть результаты запроса в виде текстового файла.

Сначала мы создадим скрипт SQL для перечисления имен наших таблиц. Вы можете так же перечислить ваши представления, хранимые процедуры и т.д., используя "sys.objects".

SELECT *

FROM sys.objects;

GO

Результаты (неполный список):



Однако нам обычно не требуется так много информации, но полезно выполнить этот скрипт, чтобы ознакомиться с возвращаемыми значениями. Вы можете ограничить результаты, чтобы вернуть только имя и тип объектов в базе данных, таким образом:

select name + ', ', TYPE

from sys.objects

Результаты:



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

select name + ', ', TYPE

from sys.objects

where type_desc != 'SYSTEM_TABLE'

AND type != 'IT'

AND type != 'SQ'

Результаты:



Как видно, мы имеем в списке три различных типа: U, V и P.

• U означает TABLE (таблица),


• V означает VIEW (представление),


• P означает PROCEDURE (процедура), например, хранимую процедуру.

Наконец, давайте вернем все таблицы, которые я создал для тестирования. Вы можете отобрать их по диапазону дат. Перед выполнением следующего скрипта переключите возвращаемый результат в режим текстового файла. Вы можете сделать это щелчком по кнопке "Results to Text" на панели инструментов или с помощью комбинации клавиш CTRL + T.

select name + ', '

from sys.objects

where type = 'U'

AND create_date >= '2022-09-28';

GO

Результаты:



Теперь вы можете скопировать и вставить результаты в вашу команду DROP TABLE.