Comment vérifiez-vous si IDENTITY INSERT est activé ou désactivé dans SQL Server?

En résumé:

• La solution de Nathan est la plus rapide:

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

  • Lors de l'utilisation D'un wrapper D'API, on peut réduire la vérification complète à la simple vérification des lignes. Par exemple, lorsque vous utilisez la propriété SqlDataReaders de C#HasRows et une construction de requête comme:

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

• La solution de Ricardo permet plus de flexibilité mais nécessite le nom d'identité de la colonne

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

• Bogdan Bodanov solution, en utilisant try/catch fonctionnerait aussi bien, mais une vérification supplémentaire devrait limiter la gestion des exceptions aux cas de IDENTITY_INSERT is already ON for table 'MyTable'. Cannot perform SET operation for table 'MyTable';

Si vous essayez de désactiver IDENTITY_INSERT pour une autre table afin d'éviter une erreur lorsque vous souhaitez définir IDENTITY_INSERT sur, ce qui suit peut également fonctionner pour vous. Comme d'autres l'ont dit sur ce thread IDENTITY_INSERT est un paramètre de session SANS VISIBILITÉ DIRECTE. Cependant, j'ai fait la découverte intéressante que SET IDENTITY_INSERT OFF ne fait pas d'erreur pour une table qui a une identité si IDENTITY_INSERT est activé ou non pour cette table. Donc il m'est venu à l'esprit que je pouvais juste appelez SET IDENTITY_INSERT ... Désactivé pour chaque table avec une identité dans la base de données. Il se sent un peu comme une solution de force brute, mais J'ai trouvé que le bloc SQL dynamique suivant a très bien fait l'affaire.

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

Si vous voulez connaître la variable de session... Bonne question, mais je ne vois pas où cette information serait utile. En exécution normale pour vérifier une réponse de table normale à un insert, cela devrait fonctionner!

-- Si vous voulez seulement savoir s'il y a identity insert sur une table donnée:

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

-- ou... Utilisez ceci si vous voulez exécuter quelque chose en fonction du résultat:

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

Amusez-vous!

Vous pouvez découvrir si identity_insert est activé ou non, et si oui pour quelle table en utilisant le code ci-dessous.

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 

Très bonne question. J'Ai le même problème. Peut-être que vous pouvez essayer de réinitialiser IDENTITY_INSERT en utilisant TRY / CATCH? Par exemple, vous effectuez le travail mais vous ne savez pas si le travail est terminé et IDENTITY_INSERT est défini sur OFF.

Pourquoi ne pas essayer:

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

Aussi, je ne suis pas sûr que cela fonctionne correctement, mais je vois que l'ajout de seulement SET IDENTITY_INSERT ... OFF n'a pas de retour d'erreur. Donc, vous pouvez définir juste au cas où à la fin SET IDENTITY_INSERT ... OFF.

Vous pouvez également utiliser la méthode ObjectProperty pour déterminer si une table a une identité:

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