23

I want to get current identity value of a specific table Like IDENT_CURRENT('table') in sql server

Hamid
  • 577
  • 1
  • 6
  • 15

8 Answers8

33

If you want to get a last identity inserted value for a particular table use the following statement:

select IDENT_CURRENT('tablename')

For example:

select IDENT_CURRENT('Employee')
User42
  • 970
  • 1
  • 16
  • 27
Samba
  • 331
  • 3
  • 2
8
SELECT IDENT_SEED(TABLE_SCHEMA+'.'+TABLE_NAME) AS Seed,
IDENT_INCR(TABLE_SCHEMA+'.'+TABLE_NAME) AS Increment,
IDENT_CURRENT(TABLE_SCHEMA+'.'+TABLE_NAME) AS Current_Identity,
TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_SCHEMA+'.'+TABLE_NAME), 'TableHasIdentity') = 1
AND TABLE_TYPE = 'BASE TABLE'
DKATyler
  • 914
  • 10
  • 16
Md. Parvez Alam
  • 4,326
  • 5
  • 48
  • 108
6

To check the identity: 

    DBCC CHECKIDENT ('[schema].[YourTableName]', NORESEED);

To reseed the identity:

    DBCC CHECKIDENT ('[schema].[YourTableName]', RESEED, value);
Bikram
  • 483
  • 6
  • 16
3
SELECT AUTOINC_SEED 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME='TableName' 
AND COLUMN_NAME='ColumnName'

from Hamid's answer is fine if what you're looking for is what the identity column's seed value is (i.e. what the first ever value of the identity column was or is going to be), but if you're looking for what the next value of an inserted row is going to be, this is the query you want to use:

SELECT AUTOINC_NEXT 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME='TableName' 
AND COLUMN_NAME='ColumnName'
Community
  • 1
  • 1
Alex
  • 3,429
  • 4
  • 36
  • 66
1

If you want it just after an INSERT, you can use 

SELECT @@IDENTITY

Otherwise you must use:

SELECT MAX(Id) FROM Table
ErikEJ
  • 40,951
  • 5
  • 75
  • 115
  • 3
    `SELECT MAX(Id) FROM Table` won't be any good if you do operations that can wipe the whole table or delete the rows that are currently the `MAX(Id)` value. – Alex Oct 15 '14 at 13:41
  • Option-1 works good after insert operation. Option-2 not recommended. – Santosh Jadi Jul 05 '18 at 07:35
1
SELECT IDENT_CURRENT('tableName or ViewName')

Ex:

SELECT IDENT_CURRENT('Student')
Gander
  • 1,854
  • 1
  • 23
  • 30
Hari
  • 117
  • 4
0
SELECT AUTOINC_SEED 
FROM INFORMATION_SCHEMA.COLUMNS 
where TABLE_NAME='tablename' 
and COLUMN_NAME='columnName'
Prahalad Gaggar
  • 11,389
  • 16
  • 53
  • 71
Hamid
  • 577
  • 1
  • 6
  • 15
  • How is this the answer?! This is whatever the identity value was when the table was created, not what its current value is. – Alex Oct 28 '14 at 09:17
  • @Alex if you fell that the answer is not correct, feel free to add your own, but do not change other's answers. – Petr Abdulin Oct 28 '14 at 09:31
  • @PetrAbdulin Fine, but correcting an answer is not "intended to address the author". You can say that my edit wasn't appropriate, but that rejection reason does not make sense. I wasn't intending to address the author at all. – Alex Oct 28 '14 at 13:04
  • It shows the seed value and the not current increment value. Of course, mostly shows the value =1 – Laxmikant Bhumkar Oct 09 '17 at 13:35
-1
DECLARE @v_identity
SELECT @v_identity = ISNULL(MAX([index]), 0) + 1
FROM [dbo].[Table]
SELECT @v_identity
Ahmed Ashour
  • 5,179
  • 10
  • 35
  • 56
test data
  • 1
  • 1