SQL table function or means to tell if identity column has been used?

Question

I was curious if there is a way to tell if an IDENTITY column has ever been incremented if there is no data within the table. (i.e. data item was inserted, then deleted)

Answer 1

For SQL Server you can use this approach:

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'

Note that you can reset the seed value in SQL Server, so the above apporach only tells you the current state of the seed, not whether there was ever any data in the table or not.