Is $IDENTITY documented and reliable in SQL Server?
$IDENTITY (and $ROWGUID) are documented in SELECT Clause (Transact-SQL).

I would caution against using it because it will throw an error when a table doesn't have an identity column. Take these two examples - the first one works (check your Results tab), but the second one throws an error:

CREATE TABLE #EmployeesWithIdentity (EmployeeID INT IDENTITY(1,1), EmployeeName VARCHAR(50));
SELECT $IDENTITY FROM #EmployeesWithIdentity;
GO

CREATE TABLE #Employees (EmployeeName VARCHAR(50));
SELECT $IDENTITY FROM #Employees;
GO


Here's what the second one produces:

Msg 207, Level 16, State 1, Line 6
Invalid column name '$IDENTITY'.


If you're looking to find which columns have an identity field, I'd use the method described in this Stack Overflow post instead:

select 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


Or, using the catalog views:

SELECT s.name,t.name,c.name
FROM sys.schemas AS s 
JOIN sys.tables AS t ON s.[schema_id] = t.[schema_id]
JOIN sys.identity_columns AS c ON t.[object_id] = c.[object_id];