Удаление всех таблиц и зависимостей SQL Server 2005

Быстрый ответ

Если вам потребовалось удалить из базы всех таблицы, процедуры, триггеры и ограничения одной командой, вам необходим утонченный скрипт, который сформирует и запустит команды DROP. Ниже представлен отрывок SQL-кода для T-SQL, особым образом предназначенный для SQL Server. Скрипт сначала отключает ограничения и триггеры, после чего удаляет представления, процедуры и таблицы. Настраивайте под свои требования и не забывайте о создании резервной копии перед выполнением:

-- Отключаем ограничения и триггеры как настоящие SQL мастера
EXEC sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL; ALTER TABLE ? DISABLE TRIGGER ALL';

-- Удаляем все представления, хранимые процедуры и таблицы, беспощадно подчищая всё после себя
EXEC sp_MSforeachtable 'IF OBJECTPROPERTY(object_id(''?''), ''IsView'') = 1 EXEC(''DROP VIEW ?'')
ELSE IF OBJECTPROPERTY(object_id(''?''), ''IsProcedure'') = 1 EXEC(''DROP PROCEDURE ?'')
ELSE EXEC(''DROP TABLE ?'')';

В данном примере активное использование утилиты sp_MSforeachtable, специфической для SQL Server. Проверьте наличие этой утилиты в вашей версии или найдите аналогичный способ циклической обработки таблиц.

Общее очищение вашей базы данных

Понимайте, что sp_MSforeachtable хорош для массового удаления объектов, но его безапелляционный подход может быть недостаточен для чуткого обращения с сложно связанными зависимостями и пользовательскими типами данных. Для точечного подхода к очистке также важно внимательно рассматривать связи между объектами и удалять их поэтапно и осмотрительно.

Ранжирование зависимостей

Зависимости могут вызывать сбои скриптов, при отсутствии грамотного контроля над ними. Для беспроблемного удаления объектов базы данных оптимально следовать такому плану действий:

1. Устраните все внешние ограничения по ключам, чтобы отделить зависимости между таблицами.
2. Затем проведите удаление остальных связанных элементов, например представлений и триггеров.
3. В заключении удалите сами таблицы, предварительно освободив их от всех зависимостей.

Узнайте, как использовать динамический SQL и системные представления для готовки соответствующих команд DROP.

Подробнее об этом расскажет наш спикер на видео

Кто здесь сегодня пишет скрипт?

-- Объявляем переменные для сборки скрипта удаления
DECLARE @DropScript NVARCHAR(MAX)
SET @DropScript = ''

-- Осуществляем поиск и удаление всех ненужных ограничений
SELECT @DropScript = @DropScript + 'ALTER TABLE ' + TABLE_SCHEMA + '.' + TABLE_NAME + 
                    ' DROP CONSTRAINT ' + CONSTRAINT_NAME + ';'
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE != 'CHECK' 

-- Запускаем собранный скрипт
EXEC sp_executesql @DropScript

-- Аналогичным путем удаляем триггеры, представления, хранимые процедуры и таблицы

-- Отображаем результаты выполнения скрипта
EXEC sp_executesql @DropScript

Безопасность – превыше всего… но не препятствие скорости!

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

BEGIN TRANSACTION
-- Ваш скрипт очистки
COMMIT TRANSACTION -- или ROLLBACK TRANSACTION, если основной план провалился

Визуализация

Простое представление себе многоквартирный дом (🏢) с проживающими в нем различными компонентами:

🏢 = [Таблицы 🛏️, Процедуры 👨‍💼, Триггеры 🦺, Ограничения 🔗]

Представим, что перед вами поставили вызов – разрушение его одним нажатием на кнопку (🔨).

🔨 ➡️ 🏢

А в в конечном итоге:

🏢 = [ ]

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

Чистим поле для обновленных баз данных

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

Каждый новый день — это снова новая база данных

Хранимая процедура сброса может быть выведена следующим путем:

USE master 
GO 

CREATE PROCEDURE ResetDatabase @DatabaseName NVARCHAR(128) 
AS 
BEGIN
  -- Логика очистки всех объектов указанной базы данных
END 
GO

Использование этой процедуры инициирует полное очищение выбранной базы данных, оставляя за собой чистую поверхность для работы.

Но умеет ли она быть креативной

Функция "Generate Scripts" в SQL Server может предложить визуально более простой подход. При выборе объектов для удаления, вы учитываете все их взаимосвязи.

Более детализированное и вдумчивое удаление объектов осуществляется с помощью динамических скриптов, например, как у Адама Андерсона. Скрипт учитывает взаимосвязи между схемами, использует DB_NAME() для точного определения целей и правильного размещения объектов в цепи зависимостей.

Полезные материалы

1. Проблема цикла foreach – Stack Overflow — обсуждение возможности удаления всех объектов базы данных.
2. DROP TABLE (Transact-SQL) – SQL Server | Microsoft Learn — подробное описание команды DROP TABLE представленное Microsoft.
3. DROP PROCEDURE (Transact-SQL) – SQL Server | Microsoft Learn — инструкция по удалению хранимых процедур в SQL Server, подготовленная Microsoft.
4. DROP TRIGGER (Transact-SQL) – SQL Server | Microsoft Learn — гид по удалению триггеров в SQL Server, предложенный Microsoft.