SET IDENTITY_INSERT

[ousoonerjoe]

How can I find if a table already has IDENTITY_INSERT set to ON so it can be turned off?

I've looked in the sys.tables, sys.databases, sys.etc for a flag but can't find anything. Tried Google, but guess I'm not wording it right.

--------------------------------------------------
“Crash programs fail because they are based on the theory that, with nine women pregnant, you can get a baby a month.” --Wernher von Braun
--------------------------------------------------

 

[gmmastros]

If you set identity_insert off on a table that is already off, no error. If you set identity_off on a table that does not have an identity column, you get an error message. So, the trick is to determine if the table has an identity column.

Here's one way...

If Exists(
          Select 1
          from   sys.columns
          Where  object_id = object_id('YourTableName')
                 And is_identity = 1
	)
	Set Identity_Insert YourTableName Off

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ousoonerjoe]

Thanks for the reply.

How do you identify another table that has it on so it can be turned off rather than waiting for the error message, since only one table at a time can have this property set to true?

--------------------------------------------------
“Crash programs fail because they are based on the theory that, with nine women pregnant, you can get a baby a month.” --Wernher von Braun
--------------------------------------------------