Find the Current Identity of All Tables in SQL
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.