Identity columns exists for a table - How to know?

Question

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

padmakumar_pga Right there with Babe Points: 766 More actions · Answer 1

Hi

u can use this statement to know about identity column in a DB .

Select * from syscolumns where status= 0x80

Padmakumar

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

where Status = 0x80 and id = object_id('TableName')

barsuk SSChampion Points: 12280 More actions · Answer 3

SELECT sysobjects.name as 'Table Name', syscolumns.NAME AS 'Identiy Column Name'

FROM syscolumns, sysobjects

WHERE syscolumns.ID = sysobjects.ID

and sysobjects.name in (select name

FROM sysobjects where xtype = 'U' )

and syscolumns.AUTOVAL IS NOT NULL

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 4

To add to the above, since accessing the system tables directly is not recommended, here is another way to check if a table has an identity column:

SELECT OBJECTPROPERTY(OBJECT_ID('owner.tablename'), 'TableHasIdentity')

To check if a column has an identity property:

SELECT COLUMNPROPERTY(OBJECT_ID('owner.tablename'), 'column_name', 'IsIdentity')