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

4

June 24, 2022 by Kenneth Fisher

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.

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

  1. ScottPletcher says:

    SELECT name AS table_name, SCHEMA_NAME(schema_id) AS schema_name
    FROM sys.tables t
    WHERE OBJECTPROPERTYEX(t.object_id, ‘TableHasIdentity’) = 1

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,740 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: