Identity Insert

• Miguel Machado

  SSC Enthusiast

  Points: 120

  More actions

  May 18, 2007 at 10:38 am

  #62505

  Hello,

  I need to know what tables, in a database, have Identity insert set to ON or OFF.

  Can anyone help me?

• -=JLK=-

  SSCertifiable

  Points: 5315

  More actions

  May 18, 2007 at 11:24 am

  #707544

  I thought this an interesting enough problem that I went and searched for an answer and found a post by Jeff Moden at http://p2p.wrox.com/topic.asp?TOPIC_ID=52760 that accomplishes what you want:

  SELECT so.Name AS TabName,

          sc.Name AS IdentityColName

     FROM SYSCOLUMNS sc,

          SYSOBJECTS so

    WHERE sc.ID = so.ID

      AND sc.ColStat & 1 = 1

  I didn't see it's equivalent here (though I assume it is from the same Jeff Moden who posts here), but I was using google to track a solution down. (I don't remember what I did before google )

  HTH,

  James.

• -=JLK=-

  SSCertifiable

  Points: 5315

  More actions

  May 18, 2007 at 11:28 am

  #707547

  Oops!  I made the same mistake as Jeff in his post.  I got distracted by trying to find how to identify the identity columns and did not follow through on determining their status once found.  Sorry couldn't find a final answer, maybe Jeff has it hidden somewhere

  If I find it I'll repost.

  James.

• -=JLK=-

  SSCertifiable

  Points: 5315

  More actions

  May 18, 2007 at 11:55 am

  #707555

  Just thought I would check, you do know that only one table can have the IDENTITY_INSERT option set to ON during any given users session don't you?

  Also if you disconnect and reconnect then none of the tables would currently have that option set to ON.

  James.