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.
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.
Another option is ident_current function. Advantage of it is a possibility of using it in a SQL query.
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