SQL SERVER – Query to Find Seed Values, Increment Values and Current Identity Column Value of the Table with Max Value of Datatype

Seven years ago, I wrote a blog post about – Query to Find Seed Values, Increment Values and Current Identity Column value of the table. It is quite a popular blog post and lots of people like it as it gives immediate details about Identity Values. SQL expert Mark Hickin has further improved this query and posted an interesting modification where the query also display upper limits of the data type used in the query.

SELECT IDENT_SEED(TABLE_SCHEMA + '.' + TABLE_NAME) AS Seed ,
IDENT_INCR(TABLE_SCHEMA + '.' + TABLE_NAME) AS Increment ,
IDENT_CURRENT(TABLE_SCHEMA + '.' + TABLE_NAME) AS CurrentIdentity ,
TABLE_SCHEMA + '.' + TABLE_NAME ,
UPPER(c.DATA_TYPE) AS DataType ,
t.MaxPosValue,
t.MaxPosValue -IDENT_CURRENT(TABLE_SCHEMA + '.' + TABLE_NAME) AS Remaining,
((
t.MaxPosValue -IDENT_CURRENT(TABLE_SCHEMA + '.' + TABLE_NAME))/t.MaxPosValue) 100 AS PercentUnAllocated
FROM INFORMATION_SCHEMA.COLUMNS AS c
INNER JOIN ( SELECT name AS Data_Type ,
POWER(CAST(2 AS VARCHAR), ( max_length 8 ) - 1) AS MaxPosValue
FROM sys.types
WHERE name LIKE '%Int'
) t ON c.DATA_TYPE = t.Data_Type
WHERE COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME,
'IsIdentity') = 1
ORDER BY PercentUnAllocated ASC

Here is the screenshot of the image. Thanks Mark – a very nice query.

SQL SERVER – Query to Find Seed Values, Increment Values and Current Identity Column Value of the Table with Max Value of Datatype extraidentity

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Identity
Previous Post
SQL SERVER – Fix Error 7202 Could not find server in sys.servers. Verify that the correct server name was specified
Next Post
SQL SERVER – Detecting Corruption with Suspect Pages Table – Notes from the Field #043

Related Posts

5 Comments. Leave new

  • Script will work perfectly for Seed=1 and increment=1
    For seed & Increment, other than 1 value, use this:

    SELECT Seed,Increment,CurrentIdentity,TABLE_NAME,DataType,MaxPosValue ,
    FLOOR((MaxPosValue -CurrentIdentity)/Increment) AS Remaining,
    100-100*((CurrentIdentity-Seed)/Increment+1) / FLOOR((MaxPosValue – Seed) /Increment+1) AS PercentUnAllocated
    FROM (
    SELECT IDENT_SEED(TABLE_SCHEMA + ‘.’ + TABLE_NAME) AS Seed ,
    IDENT_INCR(TABLE_SCHEMA + ‘.’ + TABLE_NAME) AS Increment ,
    IDENT_CURRENT(TABLE_SCHEMA + ‘.’ + TABLE_NAME) AS CurrentIdentity ,
    TABLE_SCHEMA + ‘.’ + TABLE_NAME AS TABLE_NAME ,
    UPPER(c.DATA_TYPE) AS DataType ,
    FLOOR(t.MaxPosValue/IDENT_INCR(TABLE_SCHEMA + ‘.’ + TABLE_NAME)) * IDENT_INCR(TABLE_SCHEMA + ‘.’ + TABLE_NAME) AS MaxPosValue
    FROM INFORMATION_SCHEMA.COLUMNS AS c
    INNER JOIN ( SELECT name AS Data_Type ,
    POWER(CAST(2 AS VARCHAR), ( max_length * 8 ) – 1) AS MaxPosValue
    FROM sys.types
    WHERE name LIKE ‘%Int’
    ) t ON c.DATA_TYPE = t.Data_Type
    WHERE COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + ‘.’ + TABLE_NAME), COLUMN_NAME,
    ‘IsIdentity’) = 1
    )T1
    ORDER BY PercentUnAllocated ASC

    Reply
  • Sanjay Monpara
    August 27, 2014 5:20 pm

    we can also add NextValue column by adding increment value in current value,

    IDENT_CURRENT(TABLE_SCHEMA + ‘.’ + TABLE_NAME) + IDENT_INCR(TABLE_SCHEMA + ‘.’ + TABLE_NAME) AS NextValue,

    Reply
  • Thanks Pinal and Harsh… one additional refinement… due to rules of precedence, need to enclose Increment+1 within parenthesis to get the correct PercentUnAllocated value:

    SELECT
    Seed
    ,Increment
    ,CurrentIdentity
    ,TABLE_NAME
    ,DataType
    ,MaxPosValue
    ,FLOOR((MaxPosValue -CurrentIdentity)/Increment) AS Remaining
    ,100-100*((CurrentIdentity-Seed)/(Increment+1.)) / FLOOR((MaxPosValue – Seed) /(Increment+1.)) AS PercentUnAllocated
    FROM (
    SELECT
    IDENT_SEED(TABLE_SCHEMA + ‘.’ + TABLE_NAME) AS Seed
    ,IDENT_INCR(TABLE_SCHEMA + ‘.’ + TABLE_NAME) AS Increment
    ,IDENT_CURRENT(TABLE_SCHEMA + ‘.’ + TABLE_NAME) AS CurrentIdentity
    ,TABLE_SCHEMA + ‘.’ + TABLE_NAME AS TABLE_NAME
    ,UPPER(c.DATA_TYPE) AS DataType
    ,FLOOR(t.MaxPosValue/IDENT_INCR(TABLE_SCHEMA + ‘.’ + TABLE_NAME)) * IDENT_INCR(TABLE_SCHEMA + ‘.’ + TABLE_NAME) AS MaxPosValue
    FROM INFORMATION_SCHEMA.COLUMNS AS c
    INNER JOIN (
    SELECT
    name AS Data_Type
    ,POWER(CAST(2 AS VARCHAR), ( max_length * 8 ) – 1) AS MaxPosValue
    FROM sys.types
    WHERE name LIKE ‘%Int’
    ) t ON c.DATA_TYPE = t.Data_Type
    WHERE COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + ‘.’ + TABLE_NAME), COLUMN_NAME,’IsIdentity’) = 1
    ) AS T1
    ORDER BY PercentUnAllocated ASC

    Reply

Leave a Reply