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.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

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

  1. 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

    Like

  2. 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,

    Like

  3. Pingback: SQL SERVER – Query to Find Seed Values, Increment Values and Current Identity Column Value of the Table with Max Value of Datatype – Part 2 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s