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'
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'
Comments
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 :)
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
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
This one worked right for me IF (SELECT COLUMNPROPERTY( OBJECT_ID('table_name'),'column_name','IsIdentity')) = 1 :)