identity_insert - sql server alter table identity off

¿Cómo se verifica si IDENTITY_INSERT está configurado en ON u OFF en SQL Server?

Question

He buscado esto, pero los hilos en los que parecía tendían a tener respuestas de personas que no entendían la pregunta.

Tome la siguiente sintaxis:

SET IDENTITY_INSERT Table1 ON

¿Cómo haces algo más como esto?

GET IDENTITY_INSERT Table1

No obstante, no quiero hacer nada de los datos en la base de datos o de la configuración para obtener esta información. ¡Gracias!

Answer 1

Si quieres saber sobre la variable de la sesión ... Buena pregunta, pero no puedo ver dónde esta información sería útil. En la ejecución normal para verificar una respuesta de tabla normal a un inserto, ¡esto debería funcionar!

- Si solo quieres saber si hay una inserción de identidad en una tabla determinada:

select is_identity
from sys.columns
where object_id = OBJECT_ID(''MyTable'', ''U'') and name = ''column_Name''

- O ... Usa esto si quieres ejecutar algo dependiendo del resultado:

if exists (select *
from sys.columns
where object_id = OBJECT_ID(''MyTable'', ''U'') and is_identity = 1)
... your code considering identity insert
else
... code that should not run with identity insert

¡Que te diviertas!

Answer 2

también puede usar el método ObjectProperty para determinar si una tabla tiene una identidad:

DECLARE @MyTableName nvarchar(200)
SET @MyTableName = ''TestTable''
SELECT CASE OBJECTPROPERTY(OBJECT_ID(@MyTableName), ''TableHasIdentity'') 
WHEN 1 THEN ''has identity'' 
ELSE ''no identity columns'' 
END as HasIdentity

Answer 3

Como SET IDENTITY_INSERT es una sesión sensible, se administra en el nivel del búfer sin almacenar en algún lugar. Esto significa que no necesitamos verificar el estado IDENTITY_INSERT ya que nunca usamos esta palabra clave en la sesión actual.

Lo siento, no hay ayuda para esto.

Buena pregunta :)

Fuente: Aquí

Actualización Hay formas de hacer esto, también se ve en el sitio que he vinculado, OMI, es demasiado esfuerzo para ser útil.

if

(select max(id) from MyTable) < (select max(id) from inserted)

--Then you may be inserting a record normally

BEGIN
    set @I = 1 --SQL wants something to happen in the "IF" side of an IF/ELSE
END

ELSE --You definitely have IDENTITY_INSERT on.  Done as ELSE instead of the other way around so that if there is no inserted table, it will run anyway


BEGIN
.... Code that shouldn''t run with IDENTITY_INSERT on
END

Answer 4

Muy buena pregunta. Tengo el mismo problema. ¿Puede ser que pueda intentar restablecer IDENTITY_INSERT usando TRY / CATCH? Por ejemplo, realiza el trabajo pero no está seguro de si el trabajo está terminado e IDENTITY_INSERT está establecido en OFF.

Por qué no lo intentas:

BEGIN TRY 
...
END TRY 
BEGIN CATCH
SET IDENTITY_INSERT table OFF;
END CATCH;

Además, no estoy seguro de que esto funcione correctamente, pero veo que al agregar solo SET IDENTITY_INSERT ... OFF no se devolvió el error. Por lo tanto, puede establecerlo solo en caso de que al final SET IDENTITY_INSERT ... OFF .

Answer 5

En resumen:

• La solución de Nathan es la más rápida:

  SELECT OBJECTPROPERTY(OBJECT_ID(''MyTable''), ''TableHasIdentity'');

  • cuando se utiliza un contenedor API, uno puede reducir el cheque completo a solo verificar las filas. Por ejemplo, cuando se usa la propiedad HasRows SqlDataReaders C # y una construcción de consulta como:

    SELECT CASE OBJECTPROPERTY(OBJECT_ID(''MyTable''), ''TableHasIdentity'')
           WHEN 1 THEN ''1'' ELSE NULL END

• La solución de Ricardo permite más flexibilidad pero requiere el nombre de identidad de la Columna

  SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(''MyTable'', ''U'') 
                          AND name = ''MyTableIdentityColumnName'';

• La solución de Bogdan Bodanov, usando try / catch también funcionaría, pero la verificación adicional debería limitar el manejo de excepciones a los casos en que IDENTITY_INSERT is already ON for table ''MyTable''. Cannot perform SET operation for table ''MyTable''; IDENTITY_INSERT is already ON for table ''MyTable''. Cannot perform SET operation for table ''MyTable'';

Answer 6

Si está intentando desactivar IDENTITY_INSERT para alguna otra tabla para evitar que se produzca un error cuando quiere establecer IDENTITY_INSERT, lo siguiente también puede funcionar para usted. Como otros han dicho en este hilo, IDENTITY_INSERT es una configuración de sesión sin visibilidad directa. Sin embargo, hice el descubrimiento interesante de que SET IDENTITY_INSERT OFF no tiene errores para ninguna tabla que tenga una identidad, ya sea que IDENTITY_INSERT esté activado o no para esa tabla. Así que se me ocurrió que podía simplemente llamar a SET IDENTITY_INSERT ... OFF para cada tabla con una identidad en la base de datos. Se siente un poco como una solución de fuerza bruta, pero descubrí que el siguiente bloque SQL dinámico hizo el truco muy bien.

---- make sure IDENTITY_INSERT is OFF ----
DECLARE @cmd NVARCHAR(MAX)
SET @cmd = CAST((SELECT ''SET IDENTITY_INSERT '' + 
             QUOTENAME(OBJECT_SCHEMA_NAME(t.object_id)) + ''.'' + 
             QUOTENAME(t.name) + '' OFF'' + CHAR(10)
             FROM sys.columns c 
             JOIN sys.tables t ON t.object_id = c.object_id
             WHERE c.is_identity = 1 
             ORDER BY 1 FOR XML PATH('''')) AS NVARCHAR(MAX))
EXEC sp_executesql @cmd

Answer 7

Puede descubrir si identity_insert está activado o no y, de ser así, para qué tabla usar el código siguiente.

declare @tableWithIdentity varchar(max) = '''';
SET IDENTITY_INSERT ExampleTable ON

begin try
  create table #identityCheck (id int identity(1,1))
  SET IDENTITY_INSERT #identityCheck ON
  drop table #identityCheck
end try
begin catch
  declare @msg varchar(max) = error_message()
  set @tableWithIdentity= @msg;
  set @tableWithIdentity = 
  SUBSTRING(@tableWithIdentity,charindex('''''''',@tableWithIdentity,1)+1, 10000)

  set @tableWithIdentity = SUBSTRING(@tableWithIdentity,1, charindex('''''''',@tableWithIdentity,1)-1)

  print @msg;
  drop table #identityCheck
end catch

if @tableWithIdentity<>''''
begin
  print (''Name of table with Identity_Insert set to ON: '' + @tableWithIdentity)
end
else
begin
  print ''No table currently has Identity Insert Set to ON''
end