How to check current identity value?

There are several ways of getting current IDENTITY values.
Let's create a table and examine on a sample. 

use AdventureWorks2017 go create table News( id   int identity(1,1), name  varchar(20) ); go ---- insert some records insert into News values('BBC'),('CNN'),('TRT')

1- First option @@identity

select @@IDENTITY

As you can see it returns 3. But if you open a new query tab and execute select @@IDENTITY again. It returns NULL because @@identity returns the last generated identity value on the opened connection.

2-The other option is DBCC command.

dbcc checkident('dbo.News',noreseed)    

Result Messages are:

Checking identity information: current identity value '3', current column value '3'.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

3- The third option is ident_current function.

select IDENT_CURRENT('dbo.News')

The advantage of this version is that you can use in select query. In this option we should use information_schema.tables to list all tables' identities. 

For example:

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

4- You can use sys.identity_columns system view to list all current identities.

SELECT       OBJECT_NAME(OBJECT_ID) AS TABLENAME,               NAME AS COLUMNNAME,               SEED_VALUE,               INCREMENT_VALUE,               LAST_VALUE  FROM     sys.identity_columns  where OBJECT_ID in (select object_id from sys.objects where type='U')