Recently in one of my project I needed to write a script to fetch identity column values from unknown tables being passed as parameter to a stored procedure. In the database there are many tables and identity column names are different in each table. The question is now, How to do it? We can fetch the identity column name of a table by using system views and then dynamically write a SQL statement to fetch only that column value from the table. That’s not wrong but lengthy and more lines of sql statements to be written. The simplest way to do it as below.
SELECT a.IDENTITYCOL, a.$IDENTITY, a.* FROM [Person].[Address] a
IDENTITYCOL and $IDENTITY works identical, they fetch the identity column values from a table like the real column specified in the query.
Note: If table does not have an identity column in it then an error will be thrown saying invalid column.