Установите IDENTITY_INSERT OFF для всех таблиц

У меня есть script, который создает всю базу данных и вставляет все записи в несколько десятков таблиц. Он отлично работает, если во время обработки не возникает какой-либо проблемы, а таблица остается с IDENTITY_INSERT ON, когда script выходит из строя во время вставки и до того, как он снова может быть отключен.

При этом script автоматически выходит из строя при попытке запустить его снова, при этом ошибка "IDENTITY_INSERT уже включена для таблицы xx", когда мы входим в вставку для первой таблицы.

Как отказоустойчивость, я хотел бы убедиться, что для IDENTITY_INSERT установлено значение OFF для всех таблиц, прежде чем запускать оставшуюся часть обработки в настройке script.

В качестве альтернативы мы могли бы закрыть соединение MS SQL и снова открыть его, что, как я понимаю, очистит все значения IDENTITY_INSERT для сеанса подключения.

Какой лучший способ сделать это и предотвратить ошибки "уже включен"?

Ответ 1

Динамический SQL:

select 'set identity_insert ['+s.name+'].['+o.name+'] off'
from sys.objects o
inner join sys.schemas s on s.schema_id=o.schema_id
where o.[type]='U'
and exists(select 1 from sys.columns where object_id=o.object_id and is_identity=1)

Затем скопируйте и вставьте полученный SQL в другое окно запроса и запустите

Ответ 2

EXEC sp_MSforeachtable @command1="SET IDENTITY_INSERT ? OFF"

Ответ 3

EXEC sp_MSforeachtable @command1="PRINT '?'; SET IDENTITY_INSERT ? OFF",
@whereand = ' AND EXISTS (SELECT 1 FROM sys.columns WHERE object_id = o.id  AND is_identity = 1)'

Основываясь на ответе Линна, если вы слишком ленитесь, чтобы выполнить это более чем на один шаг - это должно выполняться на всех таблицах, где есть столбец идентификаторов.

Caveat тестируется только в 2012 году, и sp_MSforeachtable, конечно, полностью не поддерживается...

Ответ 4

Основываясь на ответе @KevD - он отлично работал для отключения, но здесь больше для включения.

Чтобы отключить все вставки, в которых они должны быть отключены, используйте -

EXEC sp_MSforeachtable @command1="PRINT '?'; SET IDENTITY_INSERT ? OFF",
@whereand = ' AND EXISTS (SELECT 1 FROM sys.columns WHERE object_id = o.id  
AND is_identity = 1) and o.type = ''U'''

Чтобы включить все вставки, в которых они должны быть включены, используйте -

EXEC sp_MSforeachtable @command1="PRINT '?'; SET IDENTITY_INSERT ? ON",
@whereand = ' AND EXISTS (SELECT 1 FROM sys.columns WHERE object_id = o.id  
AND is_identity = 1) and o.type = ''U'''

Проверено на сервере Sql 2014 и 2016