Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

IDENTITY_INSERT value ON/OFF Expand / Collapse
Author
Message
Posted Saturday, April 26, 2008 4:11 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, November 17, 2014 4:04 PM
Points: 418, Visits: 785
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
Post #491092
Posted Sunday, April 27, 2008 8:33 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, October 18, 2013 4:06 PM
Points: 471, Visits: 589
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
Post #491129
Posted Monday, April 28, 2008 8:21 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 1, 2014 5:51 AM
Points: 242, Visits: 940
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
Post #491445
Posted Monday, April 28, 2008 11:05 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, October 18, 2013 4:06 PM
Points: 471, Visits: 589
that only tells you whether a column is an identity column or not. that's not what OP is asking.

---------------------------------------
elsasoft.org
Post #491610
Posted Monday, April 28, 2008 11:15 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, November 17, 2014 4:04 PM
Points: 418, Visits: 785
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.


Thanks
Jay
http://www.sqldbops.com
Post #491618
Posted Monday, April 28, 2008 12:40 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, October 18, 2013 4:06 PM
Points: 471, Visits: 589
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
Post #491654
Posted Monday, April 28, 2008 12:48 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, November 17, 2014 4:04 PM
Points: 418, Visits: 785
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
Post #491657
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse