Как очистить все системные версионные таблицы одним запросом?
У меня есть запрос, который позволяет мне удалить все строки и сбросить все столбцы идентичности всех моих таблиц, не нарушая ссылок на внешние ключи. Этот запрос отлично работает для обычных таблиц SQL Server:
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL';
EXEC sp_MSForEachTable 'DELETE FROM ?';
EXEC sp_MSForEachTable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL';
EXEC sp_MSforeachtable @command1 = 'DBCC CHECKIDENT (''?'', RESEED, 0)';
Проблема в том, что теперь, когда я использую темпоральные таблицы с системной версией, предыдущий запрос больше не работает. Все мои версионные таблицы имеют имена tbl_Foo, а каждая таблица истории имеет формат tbl_Foo_history.
Я пытался использовать что-то вроде этого:
EXEC sp_MSForEachTable 'ALTER TABLE ? SET (SYSTEM_VERSIONING = OFF)';
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL';
EXEC sp_MSForEachTable 'DELETE FROM ?';
EXEC sp_MSForEachTable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL';
EXEC sp_MSforeachtable @command1 = 'DBCC CHECKIDENT (''?'', RESEED, 0)';
EXEC sp_MSforeachtable 'ALTER TABLE ? SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = ''?''_history))';
но выдает ошибку:
SYSTEM_VERSIONING is not turned ON for table 'FOO.dbo.tbl_Foo_history'.
потому что sp_MSForEachTable, очевидно, сильно испортится при попытке удалить версию из таблицы истории.
Как мне это сделать, не указывая запросы индивидуально для каждой отдельной таблицы?
Запросы, которые вы пытаетесь выполнить, не могут выполняться в таблице каждый, только в таблицах с системной версией. Это ничем не отличается от попытки выполнить ALTER TABLE ? DROP Column SomeNonExistentColumn'. Вы не можете использовать sp_MSforeachtable для этого, вам придется написать скрипт, который работает только на нужных вам столах.
Кстати, вы можете использовать TRUNCATE TABLE, чтобы удалить все, что а также переустанавливает IDENTITY. Текущее значение IDENTITY является частью данных таблицы, а не ее схемы.
@PanagiotisKanavos Да, `sp_MSForEachTable был просто отправной точкой, так как раньше это работало для меня. Я просто понятия не имею, как написать сценарий для этого...
@PanagiotisKanavos Я не могу использовать TRUNCATE, так как выдает ошибку: Cannot truncate table 'dbo.tbl_Foo' because it is being referenced by a FOREIGN KEY constraint.
sys.tables имеет столбец temporal_type, который сообщает вам, является ли таблица временной таблицей/таблицей истории (и history_table_id указывает на таблицу истории). Вы можете проверить это заранее и сохранить результаты, хотя вам, возможно, все равно придется отказаться от sp_msforeachtable и написать свой собственный цикл курсора, в зависимости от. Кроме того, хорошенько подумайте о том, что означает для ваших темпоральных таблиц сброс их содержимого таким образом! В частности, убедитесь, что любые временные запросы не путаются при переписывании подобных вещей.
@JeroenMostert Этот запрос будет использоваться только на этапе разработки, когда я пишу парсер, который заполняет базу данных данными. Я не хочу хранить там все неверные тестовые данные после каждого запуска программы.
Если вы тестируете и вам вообще не понадобятся временные данные во время этого теста, вы также можете отключить управление версиями для всех таблиц один раз, удалить таблицы истории и включить его для всех таблиц только после того, как вы закончите с все. Таким образом, вы можете сохранить свой существующий код.
@JeroenMostert Да, это правда, это может быть одним из решений. Я просто ищу более элегантный, который может сохранить схему базы данных, как она будет на финальном этапе. Таким образом, я могу экспериментировать с запросами темпоральной таблицы, продолжая заполнять данные.
Тогда я бы сказал, что резервное копирование/восстановление, повторное развертывание из проекта базы данных или сброс в моментальный снимок технически более элегантны, чем ручная очистка таблиц. Хотя это все еще может быть более привлекательным, потому что обычно оно быстрее, чем другие решения (хотя и не так надежно). Я все же упомяну их для полноты картины.
Ответы 1
Я решил проблему, просто создав одинаковые запросы для каждой из моих таблиц с помощью макроса Vim. Это выглядит некрасиво, но на практике было очень быстро реализовано:
ALTER TABLE dbo.tbl_Foo1 SET (SYSTEM_VERSIONING = OFF);
ALTER TABLE dbo.tbl_Foo2 SET (SYSTEM_VERSIONING = OFF);
ALTER TABLE ...
ALTER TABLE dbo.tbl_Foo1 NOCHECK CONSTRAINT ALL;
ALTER TABLE dbo.tbl_Foo2 NOCHECK CONSTRAINT ALL;
ALTER TABLE ...
DELETE FROM dbo.tbl_Foo1;
DELETE FROM dbo.tbl_Foo2;
DELETE FROM ...
DELETE FROM dbo.tbl_Foo1_history;
DELETE FROM dbo.tbl_Foo2_history;
DELETE FROM ...
DBCC CHECKIDENT ('[tbl_Foo1]', RESEED, 0);
DBCC CHECKIDENT ('[tbl_Foo2]', RESEED, 0);
DBCC CHECKIDENT ...
DBCC CHECKIDENT ('[tbl_Foo1_history]', RESEED, 0);
DBCC CHECKIDENT ('[tbl_Foo2_history]', RESEED, 0);
DBCC CHECKIDENT ...
ALTER TABLE dbo.tbl_Foo1 WITH CHECK CHECK CONSTRAINT ALL;
ALTER TABLE dbo.tbl_Foo2 WITH CHECK CHECK CONSTRAINT ALL;
ALTER TABLE ...
ALTER TABLE dbo.tbl_Foo1 SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.tbl_Foo1_history));
ALTER TABLE dbo.tbl_Foo2 SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.tbl_Foo2_history));
ALTER TABLE ...
Я не уверен, что есть способ обойти это с помощью
MSForeachTable, но вы можете почерпнуть некоторые идеи из статья здесь Аарона Бертрана.