How do I find IDENTITY columns on Table using T-SQL

Is there a query I can write against an INFORMATION_SCHEMA or against the system tables to determine if a column is an identity column?

Found it, a little obscure:

SELECT obj.[name], col.[name], col.[colstat], col.*
FROM [syscolumns] col
JOIN [sysobjects] obj
ON obj.[id] = col.[id]
WHERE obj.type = 'U'
AND col.[status] = 0x80
ORDER BY obj.[name]

Does anyone know a way of doing this using an INFORMATIO_SCHEMA view?

|||I posted that sometime ago:

SELECT IsIdentity=COLUMNPROPERTY(id, name, 'IsIdentity')
FROM syscolumns WHERE OBJECT_NAME(id) = sometable_test'

Mit Information_schema views from
http://weblogs.asp.net/psteele/archive/2003/12/03/41051.aspx

select TABLE_NAME + '.' + COLUMN_NAME, TABLE_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = 'dbo'
and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') =
1
order by TABLE_NAME

HTH, Jens Suessmeyer.

http://www.sqlserver2005.,de

|||

Here is some more ( in technicolor ;-) )

USE northwind
GO

DECLARE @.tableName VARCHAR(50)
SELECT @.tableName = 'orders'

--Use COLUMNPROPERTY and the syscolumns system table
SELECT COUNT(name) AS HasIdentity
FROM syscolumns
WHERE OBJECT_NAME(id) = @.tableName
AND COLUMNPROPERTY(id, name, 'IsIdentity') = 1
GO

DECLARE @.intObjectID INT
SELECT @.intObjectID =OBJECT_ID('orders')

--Use OBJECTPROPERTY and the TableHasIdentity property name
SELECT COALESCE(OBJECTPROPERTY(@.intObjectID, 'TableHasIdentity'),0) AS HasIdentity

Denis the SQL Menace

http://sqlservercode.blogspot.com/