While SQL Server does a good job managing numbers for IDENTITY columns, a DBA may need to take a look at the current state of them. One of the questions that might be asked is what is the current IDENTITY value? That question can be asked to verify whether the value is reaching a maximum value allowed by the column datatype or to confirm IDENTITY values are aligned with maximum column values after deleting many rows.

There are a few ways of getting current IDENTITY values. Having options is very good because not all of them can be applied in all cases because of user permissions.

DBCC command

Using DBCC command is one of the options:

dbcc checkident ('dbo.Products', noreseed)
Checking identity information: current identity value '77', current column value '77'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

The command returns the current IDENTITY value and a maximum value of the column values.

ident_current function

Another option is ident_current function. Advantage of it is a possibility of using it in a SQL query.

select ident_current('dbo.Products')

ident_current can be easily used to get all identity values for all tables using a simple query:

select table_schema schemaName, table_name tableName, ident_current(table_schema + '.' + table_name) identValue
from information_schema.tables
where objectproperty(object_id(table_schema + '.' + table_name), 'TableHasIdentity') = 1