January 13, 2009 at 1:35 am
If i have a table with an identity column and i have set identity turned on how do i verify if the table is in ON/OFF mode. any help will be appreciated TIA
January 13, 2009 at 2:28 am
ishaan99 (1/13/2009)
If i have a table with an identity column and i have set identity turned on how do i verify if the table is in ON/OFF mode. any help will be appreciated TIA
SELECT *
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
COLUMNPROPERTY -- get columns where is_identity = 1
(
OBJECT_ID(QUOTENAME(table_schema) + '.' + QUOTENAME(table_name)) -- table ID
,column_name
,'isidentity'
) = 1
ORDER BY
table_name
,ordinal_position
January 14, 2009 at 9:36 pm
Are you trying to find out if a table has IDENTITY_INSERT set to ON?
January 15, 2009 at 11:18 pm
Eric Klovning (1/14/2009)
Are you trying to find out if a table has IDENTITY_INSERT set to ON?
Yes , work for same
January 18, 2009 at 8:01 pm
I guess the 1st question is "Do you really need to know if IDENTITY_INSERT is enabled on a table, or do you want to find out which table in the current session has it enabled"?
Note that only one table in a session can have IDENTITY_INSERT turned ON at an one time.
Note that:
- SET IDENTITY_INSERT does not have identity-insert turned ON
- SET IDENTITY_INSERT already has identity-insert turned ON
If you want to know which table has IDENTITY_INSERT turned ON for the current session, you could attempt to turn IDENTITY_INSERT ON for a table that you *know* does not currently have IDENTITY_INSERT turned ON - you will receive an "Identity_Insert is already ON for table database.schema.table" error, indicating which table currently has it turned on for the current session.
If you do not receive an error then there is no table with IDENTITY_INSERT turned ON for the current session (or the table you *knew* did not have identity_insert turned on, actually did have it turned ON:))
PK
January 18, 2009 at 8:07 pm
Paresh Prajapati (1/15/2009)
Eric Klovning (1/14/2009)
Are you trying to find out if a table has IDENTITY_INSERT set to ON?Yes , work for same
NO. It does not.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply