In SQL Server 2000 und früher funktioniert folgendes:

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA+'.'+TABLE_NAME), COLUMN_NAME,'IsIdentity')=1;

SELECT su.name AS TABLE_NAME, so.name AS TABLE_NAME, sc.name AS COLUMN_NAME
  FROM dbo.syscolumns AS sc
  JOIN dbo.sysobjects AS so
    ON sc.id = so.id
    JOIN dbo.sysusers AS su
      ON so.uid = su.uid
 WHERE sc.status & 0x80 = 0x80;

Im SQL Server 2005 sollte man folgendes verwenden:

SELECT su.name AS SCHEMA_NAME, so.name AS TABLE_NAME, sc.name AS COLUMN_NAME
  FROM sys.columns AS sc
  JOIN sys.objects AS so
    ON sc.object_id = so.object_id
    JOIN sys.schemas su
      ON su.schema_id = so.schema_id
 WHERE sc.is_identity = 1;