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.

 110 total views,  2 views today