April 26, 2008 at 4:11 pm
Hi,
Does any one know, how to find which user table has currently set the IDENTITY_INSERT value ON/OFF?
Ex:
TableA has SET INDENTITY_INSERT ON
I wants to check, the current status of the table IDENTITY_INSERT ON/OFF, Is this status values stored anywhere in the system tables in MS SQL Server?
Hope the above make sense...
Help me.
Have a Great day...
Jay.
Thanks
Jay
http://www.sqldbops.com
April 27, 2008 at 8:33 am
from BOL:
"If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, SQL Server 2005 returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for."
---------------------------------------
elsasoft.org
April 28, 2008 at 8:21 am
Jay, I think this is what you were afer. Whether the Identity property is set for a column is stored in the staus column of syscolumns (encoded in a hex string of course).if x'80' is on then it is an Identity column.
SELECT name, status
from syscolumns
Where id= object_ID('yourtable')
Toni
April 28, 2008 at 11:05 am
that only tells you whether a column is an identity column or not. that's not what OP is asking.
---------------------------------------
elsasoft.org
April 28, 2008 at 11:15 am
Yes, Jezemine is right.
I'm looking how to check that IDENTITY columns Current status, whether SET INDENTITY_INSERT ON/OFF for the column? I could understand, that is session/transaction sensitive, but still I'm looking for its residence address. :hehe:
Thanks
Jay
http://www.sqldbops.com
April 28, 2008 at 12:40 pm
I don't think there is a way to discover this from the metadata.
what I was trying to say in my first post is that if it's already set on some table and you try to set it on another, you'll get an error saying which table it's already set on. is this sufficient?
---------------------------------------
elsasoft.org
April 28, 2008 at 12:48 pm
Yes Jezemine, it is fine. I've managed my requirement without that details. Just curious to know.
Since it is session sensitive, could be it managed in buffer level without storing somewhere.
Thanks again for your thoughts.
Great Day...
Jay.
Thanks
Jay
http://www.sqldbops.com
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply