Friday, November 11, 2005

Find whether a column is identity or not ...

In one of the newgroup somebody was asking the way to find whether a column is identity or not. I thought I would write my answer there as an article for the benefit of those who have the same doubt.

I know of two ways of finding whether a given column is an identity column or not. Let me try and explain it ...

Sample Table structure:

Create a sample table with an identity column in it.

Create table [order_details]
OrderId int identity,
OrderName varchar(10),
UnitPrice int

Method 1: [Easiest way]

Select ColumnProperty(Object_id('order_details'), 'OrderId', 'IsIdentity')

Method 2:

For some reasons if you don't want the above method!! then try this one

Declare @colName varchar(100)
Declare @RetColName varchar(100)

Set @colName = 'OrderId' -- Specify the column name for which you want to check

--Status column = 128 means its an identity column
Select @RetColName=[name] from syscolumns where (status & 128) = 128 and id=(select id
from sysobjects where name='order_details')

If @colName = @RetColName
Print 'Its Identity'
Print 'Not an identity column'


Param said...

here is the Query to fetch all the columns which are identity?
select as tablename, as columnname
from syscolumns c, sysobjects o
where c.status = 128
and =
order by

Which is Efficent?

Vadivel said...

param, I am afraid you have understood the question wrongly!!

My post is on finding whether a "Given column" is identity or not.

I am not trying to list out all columns which are identity :)

Vadivel said...

That said, your code might not work well in all cases!! Because status is a bit field and it needs to be queried appropriately.

SO.Name as Tablename,
SC.Name as Columnname
From SysColumns SC, SysObjects SO
(SC.Status & 128) = 128 and SO.ID = SC.ID
order by

Param said...

ok, thanks for rectifying it. but can you explain me why 128 alone is appended to SC.status and not any non-zero number since you are using bit-wise operator,it can yield only 0 or 1.


>href=" ">Knight Rider DVD - The opening episode told the story of how a dying millionaire named Wilton Knight rescued a young undercover cop who had been shot in the face. After plastic surgery officer Michael Long had a new face, a new identity (Michael Knight), and a new mission in life: to fight for law and justice in the Knight’s incredible super-car, the Knight Industries Two Thousand–or KITT, for short. Get your href= >Knight Rider DVD at href=

Madhivanan said...

Other method

IF (SELECT COLUMNPROPERTY( OBJECT_ID('table_name'),'column_name','IsIdentity')) IS NOT NULL
Print 'Its Identity'
Print 'Not an identity column'

Vadivel said...

@Madhivanan :: What you have mentioned is what already there as method1 in my post :)

Anonymous said...

I believe that comparing with NULL always gives the 'Its Identity' result since COLUMNPROPERTY is a bit field and always gives a NOT NULL result. I tried it with the other two fields and I still got an 'Its Identity'.
This one worked right for me IF (SELECT COLUMNPROPERTY( OBJECT_ID('table_name'),'column_name','IsIdentity')) = 1 :)