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 (https://blog.sqlauthority.com)
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
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,
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