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

http://blog.sqlauthority.com/2014/08/27/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 Hickinhas 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) ASRemaining,
((t.MaxPosValue -IDENT_CURRENT(TABLE_SCHEMA + '.' +TABLE_NAME))/t.MaxPosValue) *100 AS PercentUnAllocatedFROM INFORMATION_SCHEMA.COLUMNS AS cINNER JOIN ( SELECT name AS Data_Type ,
POWER(CAST(2 AS VARCHAR), ( max_length * 8 ) - 1) AS MaxPosValueFROM sys.typesWHERE name LIKE '%Int') t ON c.DATA_TYPE = t.Data_TypeWHERE COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),COLUMN_NAME,'IsIdentity') = 1ORDER BY PercentUnAllocated ASC

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