How to get the current id number of a specific table in sql server compact

Question

I want to get the current id value of a specific table Like IDENT_CURRENT ('table') in SQL Server

Answer 1

SELECT AUTOINC_SEED FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='tablename' and COLUMN_NAME='columnName' 

Answer 2

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

 select IDENT_CURRENT('tablename') 

For instance:

 select IDENT_CURRENT('Employee') 

Answer 3

 SELECT IDENT_SEED(TABLE_NAME) AS Seed, IDENT_INCR(TABLE_NAME) AS Increment, IDENT_CURRENT(TABLE_NAME) AS Current_Identity, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasIdentity') = 1 AND TABLE_TYPE = 'BASE TABLE' 

Answer 4

 SELECT AUTOINC_SEED FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='TableName' AND COLUMN_NAME='ColumnName' 

from Hamid's answer is fine if what you are looking for is the value of the seed identifier of the column (that is, what the first value of the identity column was or will be), but if you are looking for what the next value of the inserted row will be, this is the query you want to use :

 SELECT AUTOINC_NEXT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='TableName' AND COLUMN_NAME='ColumnName' 

Answer 5

If you want it right after INSERT, you can use

 SELECT @@IDENTITY 

Otherwise, you should use:

 SELECT MAX(Id) FROM Table 

Answer 6

 DECLARE @v_identity SELECT @v_identity = ISNULL(MAX([index]), 0) + 1 FROM [dbo].[Table] SELECT @v_identity