clipped from: www.julian-kuiters.id.au   

SQL Server 2005

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