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.
There is a column on
sys.columns that identifies identity columns (
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.
This is also based on
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;
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 🙂