Fetch identity column values without specifying the column name

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.

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.

Prasad Sahoo

Prasad Sahoo, is an enthusiast of Microsoft technologies in general and a passionate database professional, mainly focusing on SQL Server performance tuning and business intelligence. He has contributed a long time of his career working on SQL Server and other RDBMS. If he is not busy with SQL stuff then he must be watching some Hollywood movies.

Leave a Reply

%d bloggers like this: