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.