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

Question

I searched for this, but the topics in which he appeared, as a rule, have answers from people who do not understand the question.

Take the following syntax:

SET IDENTITY_INSERT Table1 ON 

How do you do something more:

 GET IDENTITY_INSERT Table1 

I do not want to do anything with the data in the database or the settings to get this information. Thank!

Answer 1

Since SET IDENTITY_INSERT is session sensitive, it is managed at the buffer level without saving somewhere. This means that we do not need to check the IDENTITY_INSERT status, since we never use this keyword in the current session.

Sorry, no help for this.

Great question :)

Source: Here

Update There may be ways to do this, also seen on the site I am connected with, IMO, 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 

Answer 2

In short:

• Nathan's solution is the fastest:

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

  • when using the API wrapper, you can reduce all validation to string validation. For example, when using the C # SqlDataReaders HasRows and the query construct, for example:

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

• Ricardo Solution Provides More Flexibility, but Requires a Column ID

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

• Bogdan Bodanov’s solution using try / catch will work, but additional testing should limit exception handling to cases 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 3

You can find out if test_id is enabled, and if so, for which table is the code below used.

 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 

Answer 4

If you are trying to disable IDENTITY_INSERT for some other table to avoid getting an error, if you want to set IDENTITY_INSERT, the following may also work for you. As others in this thread have said, IDENTITY_INSERT is setting up a session without line of sight. However, I made an interesting discovery that SET IDENTITY_INSERT OFF does not throw an error for any table that has an identifier, whether IDENTITY_INSERT is included for this table. So it occurred to me that I could just call SET IDENTITY_INSERT ... OFF for each table with an identifier in the database. This is a bit like brute force solution, but I found that the next dynamic SQL block did this trick very nicely.

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

If you want to know about the session variable ... Good question, but I do not see where this information will be useful. Under normal execution, to check the normal response of the table to insert, this should work!

- If you want to know only if there is an identifier in this table:

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

- Or ... Use this if you want to do something depending on the result:

 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 

Good luck

Answer 6

Very good question. I have the same problem. Maybe you can try reset IDENTITY_INSERT with TRY / CATCH? For example, you are performing a task but are not sure if the task has ended and the IDENTITY_INSERT parameter is set to OFF.

Why aren't you trying:

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

Also, I'm not sure if this works correctly, but I see that adding only SET IDENTITY_INSERT ... OFF did not return an error. So you can set just in case at the end of SET IDENTITY_INSERT ... OFF .

Answer 7

you can also use the ObjectProperty method to determine if the table has an identifier:

 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