Как очистить все таблицы sql

SQL-Ex blog

Удалить все таблицы в SQL Server и сгенерировать список объектов на удаление

Добавил Sergey Moiseenko on Среда, 8 февраля. 2023

Проблема

Я создал 5 таблиц, 15 представлений и четыре хранимых процедуры в тестовой среде Microsoft SQL Server. Когда я завершил тестирование, то перенес все в рабочую среду. Теперь мне нужно удалить все объекты тестового SQL Server для подготовки следующего проекта.

Я знаю, что могу создать несколько скриптов SQL Server (DROP TABLE, DROP VIEW и DROP PROC), но необходимо ли делать это для каждого из 24 объектов. Как мне удалить все эти объекты более эффективно?

Решение

В этом руководстве мы обсудим простой вариант для удаления всех 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 означает TABLE (таблица),
V означает VIEW (представление),
P означает PROCEDURE (процедура), например, хранимую процедуру.

select name + ', ' 
from sys.objects 
where type = 'U' 
AND create_date >= '2022-09-28'; 
GO

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

Обратные ссылки

Нет обратных ссылок

Руководство по SQL. Очистка таблицы.

Для полного удаления всех данных из таблицы, в языке SQL используется команда TRUNCATE TABLE. Конечно, мы можем использовать команду DROP TABLE, для удаления данных и создать такую же таблицу, но нам придётся создавать её повторно. В подобных случаях, команда TRUNCATE TABLE крайне облегчает нам работу.

Запрос с использованием команды TRUNCATE TABLE имеет следующий вид:

 TRUNCATE TABLE имя_таблицы;

Предположим, что у нас есть таблицы developers_copy, которая содержит следующие записи:

 +----+-------------------+------------+------------+--------+ | ID | NAME | SPECIALTY | EXPERIENCE | SALARY | +----+-------------------+------------+------------+--------+ | 1 | Eugene Suleimanov | Java | 2 | 2000 | | 2 | Peter Romanenko | C++ | 3 | 3500 | | 3 | Andrei Komarov | JavaScript | 2 | 2100 | +----+-------------------+------------+------------+--------+

Допустим, нам необходимо удалить все записи из данной таблицы.
Для этого мы должны использовать следующую команду:

 mysql> TRUNCATE TABLE developers_copy;

Теперь наша таблица developers_copy пуста:

 mysql> SELECT * FROM developers_copy; Empty set (0.00 sec)

На этом мы заканчиваем изучение способа удаления всех данных из таблицы.
В следующей статье мы рассмотрим использование отображений в языке SQL.

Как очистить все таблицы базы данных

В этой статье мы покажем способы очистки и удаления таблиц базы данных MySQL. Это актуально в том случае, если у вас отсутствуют права доступа к базе данных для ее создания и удаления. Также на тот случай, если времени мало и нет времени искать и запоминать параметры базы, чтобы затем пересоздать её.

Способ 1. Умный

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

Пример запуска команды в консоле:

Способ 2. Хитрый

Еще один хитрый способ для запуска в консоле сервера

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

Способ 3. Пыховатый

Простой способ, который требует только наличия доступов в базу данных. Всего и нужно создать php-скрипт где-нибудь в публичке сайта и запустить его.

Если вам необходимо очистить таблицы от записей, а не удалять их полностью – замените в коде DROP TABLE на TRUNCATE TABLE После запуска вы увидите какие таблицы были очищены и их количество.

Не забудьте удалить скрипт с сайта, после процедуры очистки

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

Новые записи

Как авторизоваться в админке без пароля?
Аудит сайта на Битрикс. Часть 2. Проверка системы
Как снять бекап базы в Битрикс?
Подробная статья про функции отладки кода в Битрикс
Аудит сайта на Битрикс. Часть 1. Зачем нужен аудит сайта?

TRUNCATE TABLE (Transact-SQL)

Удаляет все строки в таблице или указанные секции таблицы, не записывая в журнал удаление отдельных строк. Инструкция TRUNCATE TABLE похожа на инструкцию DELETE без предложения WHERE, однако TRUNCATE TABLE выполняется быстрее и требует меньших ресурсов системы и журналов транзакций.

Синтаксис

-- Syntax for SQL Server and Azure SQL Database TRUNCATE TABLE < database_name.schema_name.table_name | schema_name.table_name | table_name >[ WITH ( PARTITIONS ( < | > [ , . n ] ) ) ] [ ; ] ::= TO

-- Syntax for Azure Synapse Analytics and Parallel Data Warehouse TRUNCATE TABLE < database_name.schema_name.table_name | schema_name.table_name | table_name >[;]

Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.

Аргументы

database_name

Имя базы данных.

schema_name

Имя схемы, которой принадлежит таблица.

table_name

Имя таблицы, которая должна быть усечена, или таблицы, из которой удаляются все строки. table_name должно быть литералом. table_name не может быть функцией OBJECT_ID() или переменной.

WITH ( PARTITIONS ( < partition_number_expression> | range> > [ , . n ] ) )

Применимо к: SQL Server (с SQL Server 2016 (13.x); до текущей версии)

Указывает секции для усечения или секции, из которых удаляются все строки. Если таблица не секционирована, аргумент WITH PARTITIONS приведет к возникновению ошибки. Если предложение WITH PARTITIONS не указано, будет усечена вся таблица.

можно указать одним из следующих способов:

Указав номер секции, например WITH (PARTITIONS (2))
Указав номера нескольких секций, разделив их запятыми, например WITH (PARTITIONS (1, 5))
Указав диапазоны секций и отдельные секции, например WITH (PARTITIONS (2, 4, 6 TO 8))
можно указать номерами секций, разделенными ключевым словом TO, например: WITH (PARTITIONS (6 TO 8)) .

Для усечения секционированной таблицы таблицы и индексы должны быть выровнены (секционированы одной функцией секционирования).

Remarks

Инструкция TRUNCATE TABLE обладает следующими преимуществами по сравнению с инструкцией DELETE.

Используется меньший объем журнала транзакций. Инструкция DELETE производит удаление по одной строке и заносит в журнал транзакций запись для каждой удаляемой строки. Инструкция TRUNCATE TABLE удаляет данные, освобождая страницы данных, используемые для хранения данных таблиц, и в журнал транзакций записывает только данные об освобождении страниц.
Обычно используется меньшее количество блокировок. Если инструкция DELETE выполняется с блокировкой строк, для удаления блокируется каждая строка таблицы. Инструкция TRUNCATE TABLE всегда блокирует таблицу (включая блокировку схемы (SCH-M)) и страницу, но не каждую строку.
В таблице остается нулевое количество страниц, без исключений. После выполнения инструкции DELETE в таблице могут все еще оставаться пустые страницы. Например, чтобы освободить пустые страницы в куче, необходима, как минимум, монопольная блокировка таблицы (LCK_M_X). Если операция удаления не использует блокировку таблицы, таблица (куча) будет содержать множество пустых страниц. В индексах после операции удаления могут оказаться пустые страницы, хотя эти страницы будут быстро освобождены процессом фоновой очистки.

Инструкция TRUNCATE TABLE удаляет все строки таблицы, но структура таблицы и ее столбцы, ограничения, индексы и т. п. сохраняются. Чтобы удалить не только данные таблицы, но и ее определение, следует использовать инструкцию DROP TABLE .

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

Для операции TRUNCATE TABLE можно выполнить откат.

Ограничения

Нельзя использовать TRUNCATE TABLE в таблицах в следующих случаях.

На таблицу ссылается ограничение FOREIGN KEY. Таблицу, имеющую внешний ключ, ссылающийся сам на себя, можно усечь.
Таблица является частью индексированного представления.
Таблица опубликована с использованием репликации транзакций или репликации слиянием.
Это темпоральная таблица с управлением версиями.
На таблицу ссылается ограничение EDGE.

Для таблиц с какими-либо из этих характеристик следует использовать инструкцию DELETE.

Инструкция TRUNCATE TABLE не может активировать триггер, поскольку она не записывает в журнал удаление отдельных строк. Дополнительные сведения см. в разделе CREATE TRIGGER (Transact-SQL).

В Azure Synapse Analytics и Система платформы аналитики (PDW):

Инструкцию TRUNCATE TABLE нельзя использовать в инструкции EXPLAIN.
Инструкцию TRUNCATE TABLE невозможно выполнить внутри транзакции.

Усечение больших таблиц

В Microsoft SQL Server существует возможность удалять или усекать таблицы, которые имеют больше 128 экстентов, не удерживая одновременные блокировки для всех экстентов, предназначенных для удаления.

Разрешения

Минимально необходимым разрешением является ALTER для table_name. Разрешения TRUNCATE TABLE назначаются по умолчанию владельцу таблицы, членам предопределенной роли сервера sysadmin , а также предопределенных ролей базы данных db_owner и db_ddladmin , и они не могут быть переданы. Тем не менее инструкцию TRUNCATE TABLE можно встроить в модуль, например в хранимую процедуру, и предоставить соответствующие разрешения этому модулю с помощью предложения EXECUTE AS .

Примеры

A. Усечение таблицы

В ходе выполнения следующего примера удаляются все данные таблицы JobCandidate . Инструкции SELECT включены до и после инструкции TRUNCATE TABLE для сравнения результатов.

USE AdventureWorks2022; GO SELECT COUNT(*) AS BeforeTruncateCount FROM HumanResources.JobCandidate; GO TRUNCATE TABLE HumanResources.JobCandidate; GO SELECT COUNT(*) AS AfterTruncateCount FROM HumanResources.JobCandidate; GO

Б. Усечение секций таблицы