How do you check if IDENTITY_INSERT is set to ON or OFF in SQL Server?

Since SET IDENTITY_INSERT is a session sensitive, it is managed in buffer level without storing somewhere. This means we do not need to check the IDENTITY_INSERT status as we never use this key word in current session.

Sorry, no help for this.

Great question though 🙂

Source: Here

Update
There are ways maybe to do this, also seen in the site I linked, IMO, it is too much effort to be useful.

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