SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


IDENTITY_INSERT value ON/OFF


IDENTITY_INSERT value ON/OFF

Author
Message
Jayakumar Krishnan
Jayakumar Krishnan
SSChasing Mays
SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)

Group: General Forum Members
Points: 660 Visits: 809
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
jezemine
jezemine
Right there with Babe
Right there with Babe (795 reputation)Right there with Babe (795 reputation)Right there with Babe (795 reputation)Right there with Babe (795 reputation)Right there with Babe (795 reputation)Right there with Babe (795 reputation)Right there with Babe (795 reputation)Right there with Babe (795 reputation)

Group: General Forum Members
Points: 795 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
toniupstny
toniupstny
Old Hand
Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)

Group: General Forum Members
Points: 386 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
jezemine
jezemine
Right there with Babe
Right there with Babe (795 reputation)Right there with Babe (795 reputation)Right there with Babe (795 reputation)Right there with Babe (795 reputation)Right there with Babe (795 reputation)Right there with Babe (795 reputation)Right there with Babe (795 reputation)Right there with Babe (795 reputation)

Group: General Forum Members
Points: 795 Visits: 589
that only tells you whether a column is an identity column or not. that's not what OP is asking.

---------------------------------------
elsasoft.org
Jayakumar Krishnan
Jayakumar Krishnan
SSChasing Mays
SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)

Group: General Forum Members
Points: 660 Visits: 809
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
jezemine
jezemine
Right there with Babe
Right there with Babe (795 reputation)Right there with Babe (795 reputation)Right there with Babe (795 reputation)Right there with Babe (795 reputation)Right there with Babe (795 reputation)Right there with Babe (795 reputation)Right there with Babe (795 reputation)Right there with Babe (795 reputation)

Group: General Forum Members
Points: 795 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
Jayakumar Krishnan
Jayakumar Krishnan
SSChasing Mays
SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)

Group: General Forum Members
Points: 660 Visits: 809
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search