Hi,

I am desperately seeking for a query that I may issue against SQL
Server2000- that will tell me whether a column is identiy or not!!

Please help!!

This might get you on the right path:

http://www.windowsitpro.com/Article/ArticleID/16091/16091.html

Show quoteHide quote

It's not really all that complicated: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_0mpl.asp

E.g.
use Northwind
go

select columnproperty(object_id('dbo.Categories'), 'CategoryID', 'IsIdentity')
go


ML

I know of two ways of doing this. Let me try and explain it ...

-- Sample Table structure
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 and id=(select id
from sysobjects where name='order_details')

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

Best Regards
Vadivel

http://vadivel.blogspot.com
http://thinkingms.com/vadivel

Show quoteHide quote

This may not work. The status column holds a bitmap and should be queried
appropriately:

where (status & 128 = 128)


ML

convert INT
A difficult Combining Rows problem
How to use OPENXML to retrieve detail lineitems
Rearrange column
SQL view deducting multiples