Identify all tables with an identity column

Question

Is there a query I can run that will show me all tables with an identity column? Trying to figure out if there are any identity based tables whose current identity is nearing upper bound of int32 or int64.

asked 2021-07-02 by user2368632

---

Answer

There is a column on sys.columns that identifies identity columns (is_identity).

Additionally, there is a IDENT_CURRENT() function that you can use to identify the most recently generated identity value for a table to determine if you’re approaching the int max. (docs)

You can simply query that to identify every table with an identity column, (and what the identity column is) by doing something like this:

SELECT 
    TableName       = o.name,
    ColumnName      = c.name,
    IdentityCurrent = IDENT_CURRENT( o.name ) ,
    TypeName        = t.name
FROM sys.objects AS o
JOIN sys.columns AS c
    ON c.object_id = o.object_id
    AND c.is_identity = 1
JOIN sys.types AS t
    ON t.system_type_id = c.system_type_id
WHERE o.type = 'U'; --User tables

You could add to the WHERE clause to add additional criteria to capture only tables that are getting too close for comfort as basis for generating your alert.

answered 2021-07-02 by Andy Mallon