How do I tell if identity_insert is turned on and if so what table?

TLDR; The code to do this is below but it’s a bit hokey and probably isn’t worth it. It’s pretty unlikely that you’d ever need it.

I just had someone ask me this question. It’s an interesting question and I had absolutely no clue, so I did a bit of research. It can be done. There are probably other ways to do this (all code related) but here’s the one I came up with.

CREATE TABLE #test (Id INT NOT NULL IDENTITY(1,1));
DECLARE @TableName nvarchar(4000);
BEGIN TRY
SET IDENTITY_INSERT #test ON;
PRINT 'Identity_Insert is not turned on.';
END TRY
BEGIN CATCH
SET @TableName = SUBSTRING(ERROR_MESSAGE(),42,9999);
SET @TableName = SUBSTRING(@TableName,1,CHARINDEX('''',@TableName)-1);
PRINT @TableName;
END CATCH
DROP TABLE #test;

Basically the answer is in the error. If you don’t get an error it’s not turned on, and if you do the error says the table name it’s on on. (That sounded a bit weird hu?) A little bit of parsing and you have your answer. I created a temp table specifically for the purpose so the code is generic and can be run on any database, and so the answer is a guaranteed no if I don’t get an error.

All of that said, and it doesn’t really matter. IDENTITY_INSERT is generally used in ad hoc code and when it isn’t it probably isn’t going to be a big piece of application code. If it’s ad hoc then you’re sitting right there and can see the error. Just turn it off on the table it’s turned on on (yea, still sounds funny) and move on. If it’s part of a piece of code then the fact that you’re seeing an error means that you didn’t turn it off after using it and you need to fix your code. (If it wasn’t clear, you should turn off IDENTITY_INSERT when you are done with it.)

Also, IDENTITY_INSERT is session specific. If you’re having a problem then re-connecting your session would reset it no matter what. Also the fact that it’s session specific means it’s not possible for Bob the Jr DBA to leave IDENTITY_INSERT turned on somewhere and block Sarah the database dev from being able to get her work done. The only person it’s affecting is you.