How to get current identity number of specific table in sql server compact

sql, sql-server-ce

Solution

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'

Problem

Original source