IDENT_CURRENT() returns the last IDENTITY value inserted into a given table, from any session or scope.
7> CREATE TABLE MyTable (
8> key_col int NOT NULL IDENTITY (1,1),
9> abc char(1) NOT NULL
10> )
11> INSERT INTO MyTable VALUES ('a')
12> INSERT INTO MyTable VALUES ('b')
13> INSERT INTO MyTable VALUES ('c')
14> SELECT * FROM MyTable ORDER BY key_col
15>
16> SELECT
17> IDENT_CURRENT ('MyTable')
18>
19> drop table MyTable
20> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
key_col abc
----------- ---
1 a
2 b
3 c
(3 rows affected)
----------------------------------------
3
(1 rows affected)
1>