Know your Identity

Often we create tables that have identity columns. An identity column is a special column in SQL Server that can automatically take on values when an insert operation is done on the table. What if you want to find out whether a table contains an identity column?? SQL Server stores this information in the syscolumns table, but you can access this information in many ways. In this article, we will see the various ways to access information about whether a table has an identity column.

Before we start off with the methods, let's first create a table that has an identity column, like this:


CREATE TABLE samp_test
(
	colA	INT IDENTITY,
	colB	VARCHAR(10)
)

Method A

In the first method, we will fetch this information from the syscolumns table. This table contains a column called status that contains various values. If the value in this column contains hexadecimal 80, then it means that the corresponding column contains an identity column. Here is how we can check this:


IF (EXISTS (SELECT name FROM syscolumns WHERE
	OBJECT_NAME(id) = 'samp_test' AND status = 0x80))
	PRINT 'Table has an identity column'
ELSE
	PRINT 'Table does not have an identity column'

Method B

In the second method, we will see whether a table has an identity column using the COLUMNPROPERTY function. This function takes an argument IsIdentity that indicates whether the column is an identity column. Here is how we can do the check using this function:


IF (EXISTS (SELECT myTemp.IsIdentity FROM
	(SELECT IsIdentity=COLUMNPROPERTY(id, name, 'IsIdentity')
		FROM syscolumns WHERE OBJECT_NAME(id) = 'samp_test') AS myTemp
	WHERE myTemp.IsIdentity = 1))
	PRINT 'Table has an identity column'
ELSE
	PRINT 'Table does not have an identity column'

This method is slightly convoluted since the COLUMNPROPERTY function takes the name of the column as the parameter. Thus, if we want to make a generic sort of a call, we need to iterate through each column of a table using the syscolumns table and then make a call to the COLUMNPROPERTY function. Finally we then run an EXISTS check over this derived table to check for identity.

Method C

In the final method, we will use the OBJECTPROPERTY function to check whether a table has an identity column. Here is how we can do the check using this function:


IF (OBJECTPROPERTY(OBJECT_ID('samp_test'), 'TableHasIdentity') = 1)
	PRINT 'Table has an identity column'
ELSE
	PRINT 'Table does not have an identity column'

This is the easiest of the lot. The OBJECTPROPERTY function takes as parameter the name of the object to check (a table in this case) and then the name of the property. We use the OBJECT_ID function to get the ID of the object. We then use the TableHasIdentity property to check if the table has an identity column. One difference between this method and the others is that this method does not give the name of the column which is the identity column, which is possible using the other two methods.