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

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. You can read the blog post over Query to Find Seed Values, Increment Values and Current Identity Column Value of the Table with Max Value of Datatype.

However, this query has a small limitation that it only works in those cases when increment value is 1 and seed is 1. However, if you have any other value as identity or seed, you will need a bit more modification in the script. SQL Server Expert and Guru Harsh has provided amazing script where he has provided query with the said adjustment.

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

Here is the screenshot of the image. Thanks Harsh- a very nice query.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.