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.




Leave a Reply

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