0

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.

0

3 Answers 3

7

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.

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;
1

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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.