IDENTITY_INSERT

happygv · November 24th, 2006, 08:58 AM

Hi,

Does anyone know where the status of IDENTIY_INSERT is stored in MS SQL server? I mean where I can get the status of this being ON or OFF and for which table is that last set. I ams ure it is somewhere in the system tables. But didn't get a clue. If someone know please post it here.

Thanks,

_________________________
- Vijay G
Strive for Perfection

SQLScott · November 24th, 2006, 10:32 AM

I have the system table schema poster at my office. I can take a look at it and let you know. I'm sure it is on there...

Scott Klein
Author - Professional SQL Server 2005 XML
http://www.wrox.com/WileyCDA/WroxTit...764597922.html

Jeff Moden · November 24th, 2006, 10:51 AM

It's actually buried in a status column... this will dig it out...

Code:

 SELECT so.Name AS TabName,
        sc.Name AS IdentityColName 
   FROM SYSCOLUMNS sc,
        SYSOBJECTS so
  WHERE sc.ID = so.ID
    AND sc.ColStat & 1 = 1

--Jeff Moden

happygv · November 27th, 2006, 10:46 AM

Jeff,

I think you got it the other way. One can always list the identity columns using system tables. But my question is using system tables how do I find which table has currently set the IDENTITY_INSERT value ON/OFF.

Or Let me rephrase it.

How do I find if the IDENTITY_INSERT is ON or OFF using system tables?

Eg: say I set IDENTITY_INSERT ON for some table and I am not setting it OFF, If the same is set ON again for someother table or for the same table, sql server errs saying it was set ON for so and so table and I am trying to set it ON/OFF for so and so table.

I am looking to break that. Before it errs me out on that, Let me check the status of IDENTITY_INSERT, if ON or OFF, and if ON for which table. i suspect this being set somewhere in some system table and I am kind of running out thoughts, so I posted here. Let me see if I can find it out today myself.

Thanks for your effort.
cheers.

_________________________
- Vijay G
Strive for Perfection

Jeff Moden · November 27th, 2006, 02:10 PM

You're right... The code I posted only show what the IDENTITY columns are and not whether IDENTITY_INSERT is on or off.

Two things... first, I don't think it matters because it is session sensitive.
Second, sorry, I have no idea... it's not in any of the main system tables that I looked at.

--Jeff Moden