SQL Server 2005 List all IDENTITY columns
Here is a simple bit of T-SQL that will display the names of all the IDENTITY columns in a database:
SELECT SCHEMA_NAME( OBJECTPROPERTY( OBJECT_ID, 'SCHEMAID' )) AS
SCHEMA_NAME,
OBJECT_NAME( OBJECT_ID ) AS TABLE_NAME,
NAME AS COLUMN_NAME
FROM SYS.COLUMNS
WHERE COLUMNPROPERTY(OBJECT_ID, NAME, 'IsIdentity') = 1
OBJECT_NAME( OBJECT_ID ) AS TABLE_NAME,
NAME AS COLUMN_NAME
FROM SYS.COLUMNS
WHERE COLUMNPROPERTY(OBJECT_ID, NAME, 'IsIdentity') = 1
