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

Solution 1:

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

Solution 2:

In summary:

• Nathan's solution is the fastest:

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

  • when using an API wrapper, one can reduce the entire check to just checking for rows. For instance when using C#'s SqlDataReaders property HasRows and a query construct like:

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

• Ricardo's solution allows more flexibility but requires the Column's identity name

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

• Bogdan Bodanov solution, using try/catch would work as well, but additional checking should confine exception handling to cases of IDENTITY_INSERT is already ON for table 'MyTable'. Cannot perform SET operation for table 'MyTable';

Solution 3:

You can discover whether or not identity_insert is on, and if so for what table using the code below.

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