How to check sqlsever table data type identity status ?

Unlike in Oracle, sqlserver has an special data type in order by make identity growth. But what about if the number is exceed or approaching the limited ?

Yes. there will show an error like :

Arithmetic overflow error converting IDENTITY to data type int. Arithmetic overflow occurred.

In fact, we could monitor in any time..

Script:

select a.TABLE_NAME,a.COLUMN_NAME,a.DATA_TYPE,

(CASE a.DATA_TYPE when 'int' then 'limited between -2147483648 and 2147483647'

when 'bigint' then 'limited between -9223372036854775808 and 9223372036854775807'

when 'smallint' then 'limited between -32768 and 3767'

when 'decimal' then 'limited between -10^38 and 10^38 - 1'

END

) as "Description",

c.INCREMENT_VALUE,

c.LAST_VALUE as "current identity",

' ',

(CASE a.DATA_TYPE when 'int' then 2147483647

when 'bigint' then 9223372036854775807

when 'smallint' then 3767

when 'decimal' then 9999999999999999999999999999999

END

) as "MAX value"

from INFORMATION_SCHEMA.COLUMNS a inner join

SYS.objects b on a.TABLE_NAME=b.name

inner join SYS.IDENTITY_COLUMNS c on b.object_id=c.object_id

where COLUMNPROPERTY(object_id(a.TABLE_SCHEMA+'.'+a.TABLE_NAME), a.COLUMN_NAME, 'IsIdentity') = 1

and a.COLUMN_NAME=c.name and a.table_name=OBJECT_NAME(c.OBJECT_ID)

order by a.TABLE_NAME

You could see like this following result.