Comment vérifier si IDENTITY_INSERT est réglé sur ON ou OFF dans SQL Server ?

Question

Je l'ai cherché, mais les fils de discussion dans lesquels il apparaissait avaient tendance à avoir des réponses de personnes qui ne comprenaient pas la question.

Prenez la syntaxe suivante :

SET IDENTITY_INSERT Table1 ON

Comment faire quelque chose de plus comme ça :

GET IDENTITY_INSERT Table1

Je ne veux pas faire quoi que ce soit aux données de la base de données ou aux paramètres pour obtenir cette information. Merci !

Answer 1

Depuis SET IDENTITY_INSERT est sensible à la session, il est géré au niveau du tampon sans être stocké quelque part. Cela signifie que nous n'avons pas besoin de vérifier le IDENTITY_INSERT car nous n'utilisons jamais ce mot clé dans la session actuelle.

Désolé, je ne peux pas vous aider.

Bonne question cependant :)

Fuente: Ici

Mise à jour Il existe peut-être des moyens de le faire, comme le montre le site que j'ai mis en lien, mais, selon moi, cela demande trop d'efforts pour être utile.

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 2

En résumé :

• La solution de Nathan est le plus rapide :

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

  • en utilisant un wrapper API, on peut réduire l'ensemble de la vérification à la seule vérification des rangs. Par exemple, lorsque l'on utilise l'API C# SqlDataReaders propriété 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 requiert le nom d'identité de la colonne

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

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

Answer 3

Si vous essayez de désactiver IDENTITY_INSERT pour une autre table afin d'éviter d'obtenir une erreur lorsque vous souhaitez activer IDENTITY_INSERT, la méthode suivante peut également vous convenir. Comme d'autres l'ont dit dans ce fil de discussion, IDENTITY_INSERT est un paramètre de session sans visibilité directe. Cependant, j'ai fait une découverte intéressante : SET IDENTITY_INSERT OFF n'entraîne pas d'erreur pour une table ayant une identité, que IDENTITY_INSERT soit ON ou non pour cette table. Il m'est donc apparu que je pouvais simplement appeler SET IDENTITY_INSERT ... OFF pour chaque table ayant une identité dans la base de données. Cela ressemble un peu à une solution de force brute, mais j'ai trouvé que le bloc SQL dynamique suivant faisait très bien 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

Answer 4

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

-- Si vous voulez seulement savoir s'il y a une insertion d'identité 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 bien !

Answer 5

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