Identify all tables with an identity column

All we need is an easy explanation of the problem, so here it is.

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.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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.

Method 2

This is also based on sys.identity_columns:

SELECT 
    Q1.*,
    inserts_remaining = 
        CONVERT(decimal(38), 
            (V.max_value - V.last_value) / V.increment_value),
    percentage_used = 
        CONVERT(decimal(5, 2),
        V.last_value * 100 / V.max_value)
FROM 
(
    SELECT
        [object_name] =
            OBJECT_SCHEMA_NAME(IC.[object_id]) + 
            N'.' +
            OBJECT_NAME(IC.[object_id]),
        column_name = IC.[name],
        data_type = TYPE_NAME(IC.user_type_id),
        IC.seed_value,
        IC.increment_value,
        IC.last_value,
        max_value = 
            CASE SQL_VARIANT_PROPERTY(IC.seed_value, 'BaseType')
                WHEN N'tinyint' THEN '255'
                WHEN N'smallint' THEN '32767'
                WHEN N'int' THEN '2147483647'
                WHEN N'bigint' THEN '9223372036854775807'
                ELSE REPLICATE('9', IC.[precision])
            END
    FROM sys.identity_columns AS IC
    WHERE
        OBJECTPROPERTYEX(IC.[object_id], 'IsUserTable') = 1
) AS Q1
CROSS APPLY
(
    VALUES
        (
            CONVERT(decimal(38), Q1.last_value),
            CONVERT(decimal(38), Q1.increment_value),
            CONVERT(decimal(38), Q1.max_value)
        )
) AS V (last_value, increment_value, max_value)
ORDER BY
    percentage_used DESC;

Method 3

I wrote a script (based on sys.identity_columns) a long time ago for this purpose.

I made an attempt at including an estimate for the date you will run out of identity values, assuming a default insert rate (you can configure this option).

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply