Getting the Identity

In the article titled Know your Identity, we saw the various methods that can be used to check if a table has an identity column. Consider a different requirement. Suppose you need to get the content of the identity column of a table and you do not know the name of the identity column. How do you do it?? There are different methods available:

You could find the name of the identity column and then use dynamic SQL to execute a query that returns the result.
You could use the built-in function IDENTITYCOL which returns the contents of the identity column.

Let's see how we can use each of these methods. Before we proceed further, let's create a sample table and insert some rows into it. Here is the script for the same:


CREATE TABLE samp_test
(
	colA	INT IDENTITY,
	colB	VARCHAR(10)
)
GO
INSERT INTO samp_test VALUES ('Record - 1')
INSERT INTO samp_test VALUES ('Record - 2')
INSERT INTO samp_test VALUES ('Record - 3')
INSERT INTO samp_test VALUES ('Record - 4')
GO

Method A

In the first method, we will determine the name of the identity column and then execute a query to fetch the values. Here is the script for the same:


DECLARE @idColName VARCHAR(100)
DECLARE @sqlString VARCHAR(1000)
BEGIN
	SELECT @idColName = name FROM syscolumns WHERE
		OBJECT_NAME (id) = 'samp_test' AND
		status = 0x80

	IF (@idColName IS NOT NULL)
	BEGIN
		SET @sqlString = 'SELECT ' + @idColName + ' FROM samp_test'
		EXEC (@sqlString)
	END
END

If you execute the above code snippet, you will get the contents of the colA column displayed.

Method B

In the second method, we will use the built-in function called IDENTITYCOL. The function is resolved as a reference to the column in the table having the IDENTITY property. Here is the script for the same:


SELECT IDENTITYCOL FROM samp_test

If you execute this snippet, you will get the contents of the colA column displayed. We can see that using the IDENTITYCOL function is arguably more simpler than finding the name of the identity column and then executing a query against that column using dynamic SQL.

Home