sql - Establezca IDENTITY_INSERT OFF para todas las tablas

sql server 2012 set identity_insert

Question

Tengo un script que crea una base de datos completa e inserta todos los registros en unas pocas docenas de tablas. Funciona muy bien, a menos que haya algún problema durante el procesamiento, y una tabla queda con IDENTITY_INSERT ON, cuando el script falla durante la inserción y antes de que se pueda establecer en OFF nuevamente.

Cuando esto sucede, el script falla automáticamente al intentar ejecutarlo de nuevo, con el error "IDENTITY_INSERT ya está activado para la tabla xx" a medida que avanzamos en la inserción de la primera tabla.

Como solución de seguridad, me gustaría asegurarme de que IDENTITY_INSERT esté desactivado para todas las tablas, antes de ejecutar el resto del procesamiento en el script de instalación.

Como alternativa, quizás podríamos cerrar la conexión MS SQL y abrirla de nuevo, lo que, según tengo entendido, borrará todos los valores de IDENTITY_INSERT para la sesión de conexión.

¿Cuál es la mejor manera de hacer esto y evitar los errores "ya activados"?

Answer 1

Basándose en la respuesta de @ KevD: funcionó bien para deshabilitar, pero aquí hay más para habilitar también.

Para deshabilitar todas las inserciones de identidad donde necesitan ser deshabilitadas, use -

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''''''

Para habilitar todas las inserciones de identidad donde necesitan estar habilitadas, use -

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''''''

Probado en el servidor Sql 2014 y 2016

Answer 2

SQL dinámico:

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)

Luego copie y pegue el SQL resultante en otra ventana de consulta y ejecútelo

Answer 3

Tuve un problema similar, pero preferiría no utilizar procedimientos almacenados no documentados en la producción. Para automatizar esto, me basé en la respuesta de @John Dewey y la puse en un cursor. Esto itera más de 699 tablas en 407 ms.

DECLARE @sql NVARCHAR(500) -- SQL command to execute

DECLARE sql_cursor CURSOR LOCAL FAST_FORWARD FOR
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)

OPEN sql_cursor
FETCH NEXT FROM sql_cursor INTO @sql

WHILE @@FETCH_STATUS = 0
    BEGIN
        EXECUTE sp_executesql @sql  --> Comment this out to test
        -- PRINT @sql   --> Uncomment to test or if logging is desired
        FETCH NEXT FROM sql_cursor INTO @sql
    END

CLOSE sql_cursor
DEALLOCATE sql_cursor

Si está en contra de los cursores, también podría transformarse fácilmente en un ciclo while.

Answer 4

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)''

Sobre la base de la respuesta de Lynn, en caso de que sea demasiado perezoso para realizar esto en más de un paso, esto debería ejecutarse en todas las tablas donde haya una columna de identidad.

Caveat solo se prueba en 2012 y sp_MSforeachtable por supuesto no está soportado ...

Answer 5

EXEC sp_MSforeachtable @command1="SET IDENTITY_INSERT ? OFF"