How to get the current value of a identity column -SQL

This failrly does what you want....
and gets it for all tables --
use constraints for what you want

SELECT OBJECT_NAME(OBJECT_ID) AS TABLENAME,
NAME AS COLUMNNAME,
SEED_VALUE,
INCREMENT_VALUE,
LAST_VALUE
FROM SYS.IDENTITY_COLUMNS
WHERE LAST_VALUE IS NOT NULL
ORDER BY 2