Identifying indentity column?

Question

I have found out how to determine what columns are are primary key column of a given table by using this query:

 SELECT CONSTRAINT_NAME, COLUMN_NAME
 FROM
 INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
 WHERE TABLE_NAME='tablename_here' AND
 CONSTRAINT_NAME LIKE 'PK_%'

I can find out what the identity seed and increment is by using this query:

SELECT IDENT_SEED('tablename_here'), IDENT_INCR('tablename_here')

I cant use the constraint information, because a primary key constraint can be across multiple columns. And i cant seem to find any Transact SQL function to give my the identity information.

Can anybody help me understand how to find the identity information?

Im using SQL Server 2000.

Answer 1

To find the IDENTITY column in a given table you can use this:

SELECT * 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME='tablename_here' 
AND COLUMNPROPERTY(OBJECT_ID('tablename_here'),COLUMN_NAME,'IsIdentity') = 1

Answer 2

You can use the COLUMNPROPERTY function to check whether a column uses the identity property.

Answer 3

SELECT sys.tables.name, sys.columns.name
FROM sys.tables
INNER JOIN sys.columns
ON sys.tables.object_id = sys.columns.object_id
WHERE is_identity = 1
AND sys.tables.name = 'MyTable'