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'
Else
Print 'Not an identity column'

8 comments:

Param said...

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

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.

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

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.

said...

>href="http://www.great-tv-shows.com/knightrider/main.html ">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=http://www.great-tv-shows.com/knightrider/main.html >Knight Rider DVD at href=http://www.great-tv-shows.com/knightrider/main.html

Madhivanan said...

Other method

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


http://sqlblogcasts.com/blogs/madhivanan

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 :)