Find the Current Identity of All Tables in SQL

Steve Fenton
20th February 2017

If you ever need to get a list of all your tables and their current identity value (i.e. you want to know CHECKIDENT NORESEED for all your things) you can run this query.

The query should be executed against the database you are interested in:

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

The “Id” column shows the current value.