Skip to content
Home ยป How to get identity column without using actual column name?

How to get identity column without using actual column name?

Suppose you are asked to find out Identity column used in a table and get its values while you do not know table column names and which columns are having an identity.





Lets take an example to demonstrate scenario, First we create a table having identity column.

CREATE TABLE #T (SNO INT IDENTITY(1,1) , REMARKS VARCHAR(50))
INSERT INTO #T (REMARKS) VALUES ('Accepted')

then we insert one sample records into table.

SELECT * FROM #T

We have created a table #T having identity column – SNO .

Following statement returns identityย  columns with values from a table without using column name in query.

SELECT $IDENTITY FROM #T

As we see, It returns identity column name with values even without using any column name in T-SQL Query.

$IDENTITY returns error if table does not have an identity column.

Now we create a table that does not have any identity column.

CREATE TABLE #T1 (SNO INT , REMARKS VARCHAR(50))
INSERT INTO #T1 (SNO,REMARKS) VALUES (1,'Accepted')

SELECT $IDENTITY FROM #T

As you can see, this time it returns an error as we do not have any identity column in our table.




 1,344 total views,  1 views today

1 thought on “How to get identity column without using actual column name?”

  1. Thank you!
    This solved a problem I had when retrieving the identity value using Output.
    Now I can use INSERTED.$identity instead of the specific identity column name!

    Example:

    CREATE TABLE #RowsInserted (Id INT);
    INSERT INTO MyTable (ColOne, ColTwo)
    OUTPUT INSERTED.$identity INTO #RowsInserted
    values (‘Val1′,’Val2’);
    SELECT Id FROM #RowsInserted;

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.