Primary Key Capacity

Use the following SQL to determine how "full" your primary keys are, based on the number of existing values, and the data type used:

select

       '[' + p.TABLE_SCHEMA + '].[' + p.TABLE_NAME + ']' as [table]

       ,c.COLUMN_NAME

       ,ident_current(p.TABLE_SCHEMA + '.' + p.TABLE_NAME) as MaxID

       ,cls.DATA_TYPE

       ,cast(100 - isnull(ident_current(p.TABLE_SCHEMA + '.' + p.TABLE_NAME), 0) * 100 /

                     case cls.DATA_TYPE

                           when 'int' then 2147483647

                           when 'smallint' then 32767

                           when 'tinyint' then 128

                     end as decimal(4, 1))

       as [% of range left]

from

       INFORMATION_SCHEMA.TABLE_CONSTRAINTS p

       inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE c

              on c.TABLE_NAME = p.TABLE_NAME

              and c.CONSTRAINT_NAME = p.CONSTRAINT_NAME

       inner join INFORMATION_SCHEMA.COLUMNS cls

              on c.TABLE_NAME = cls.TABLE_NAME

              and c.COLUMN_NAME = cls.COLUMN_NAME

where

       p.CONSTRAINT_TYPE = 'PRIMARY KEY'

       and cls.DATA_TYPE in ('int', 'smallint', 'tinyint')

order by

       5 asc