deutschsprachigen Spieler: 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