How To Check Identity Column In SQL Server

There are multiple ways to check if a column is identity in SQL Server. We will discuss each approach individually.

How to check if column is identity in SQL Server

Approach-1: Using SQL Server Management Studio UI

This is the most basic approach that you can utilize.

For example, I have an Orders table with an identity column named OrderID. We need to follow the steps below to check whether it is identity.

1. Right-click on the column name and select the Properties option.

2. See the Identity = True, Identity Seed = 1, Identity Increment = 1. When the Identity value is true, it states it is an Identity column.

If the column is not an identity column, the Identity value will be False, as shown in the screenshot below.

Check out: How To Insert Values Into Table With Identity Column In SQL Server

Approach-2: Using the sys.identity_columns

We can execute the sys.identity_columns mentioned in the SQL query below. My table name is Orders.

SELECT *
FROM sys.identity_columns
WHERE OBJECT_NAME(object_id) = 'Orders'

After executing the above query, I got the expected output, as shown in the screenshot below. Where it is showing, the column name is OrderID, is_identity = 1, which means True, then the seed_value is 1, and the increament_value is 1, which means it is an identity column.

You can also use the below query to get the lists of identity columns from all the tables in your database.

SELECT
  o.name AS MyTableName
, columns.name AS IdentityColumnName
, is_identity, seed_value, increment_value
FROM
  sys.identity_columns AS columns
  INNER JOIN sys.objects AS o
    ON o.object_id = columns.object_id

After executing the above query, I got the expected output, as shown below.

Check out: How To Add Identity Column To Existing Table In SQL Server

Approach-3: Using the sys.columns

We can also use the sys.columns, as mentioned in the below query, to retrieve all the columns, including the identity columns, from a specified table. Here, Orders is the name of my table.

SELECT name, is_identity
FROM sys.columns
WHERE OBJECT_NAME(object_id) = 'Orders'

After executing the above query, I got the expected output, as shown in the screenshot below.

You can also use the below query to get the lists of identity columns from all the tables in your database.

SELECT
   o.name AS MyTableName
, columns.name AS IdentityColumnName
, is_identity
FROM
   sys.columns
   INNER JOIN sys.objects AS o
     ON o.object_id = columns.object_id
WHERE sys.columns.is_identity = 1;

Conclusion

Note that identity columns in SQL Server automatically generate unique numeric values. When checking for identity columns, verifying their properties, such as seed value and increment, is also a perfect practice to ensure they’re configured correctly for your requirements.

You may also like following the articles below.

• How to turn off identity column in SQL Server
• How to reset identity column value in SQL Server