SQL SERVER – Query to Find Seed Values, Increment Values and Current Identity Column value of the table

Following script will return all the tables which has identity column. It will also return the Seed Values, Increment Values and Current Identity Column value of the table.

SELECT IDENT_SEED(TABLE_NAME) AS Seed,
IDENT_INCR(TABLE_NAME) AS Increment,
IDENT_CURRENT(TABLE_NAME) AS Current_Identity,
TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasIdentity') = 1
AND TABLE_TYPE = 'BASE TABLE'

Reference : Pinal Dave (https://blog.sqlauthority.com)

SQL Function, SQL Identity, SQL Index, SQL Scripts, SQL Server, SQL System Table
Previous Post
SQL SERVER – Raid Configuration – RAID 10
Next Post
SQL SERVER – 2005 Query Analyzer – Microsoft SQL SERVER Management Studio

Related Posts

20 Comments. Leave new

  • Da Vi ebem majkata na site sto nemat da go razberete ova !!!!

    Reply
  • Adrian Bleach
    October 10, 2007 8:42 am

    Thanks Pinal Dave – very useful crib. Managed to find the tables with 10000 increment (set in error) in less than a minute from typing the request into google. Sometimes the magical interweb truly comes up trumps :-)

    Thanks

    Adrian Bleach

    Reply
  • Thanks Pinal,

    Was really hellpful in one my projects.. am under..

    Reply
  • i want to know whether identity column is on or off
    the above query will retrieve all identity column whether it is on or off..

    i want identity column on list only.

    Reply
  • How about if it’s not an identity column?

    Reply
  • Hi Pinal,

    I am using temp table in the stored procedure . The temp table is having identity column.

    i am using the temp table in a loop. i.e. everytime i insert the data into temp table and delete the table, but as usual the identity column retains the value, is there any solution to reseed the value of the identity column of temporary table.

    FYI i tired:
    1. DBCC CHECKIDENT
    2. delete from @temptable

    is there any way out..?

    Reply
    • Frank Cardona
      April 19, 2011 10:18 pm

      Use it like this:
      DBCC CHECKIDENT( your_table_name, RESEED, new_value )

      new_value = 0 so de identity starts fresh

      Reply
  • Hi Pinal,
    I just tried you query and discovered that the OBJECT_ID(), IDENT_SEED(), IDENT_INCR(), and IDENT_CURRENT() functions require the table name be prefixed with schema name, if the schema is something other than the default DBO. This modified query below now works for tables contained in a user defined schema.

    SELECT IDENT_SEED(TABLE_SCHEMA+’.’+TABLE_NAME) AS Seed,
    IDENT_INCR(TABLE_SCHEMA+’.’+TABLE_NAME) AS Increment,
    IDENT_CURRENT(TABLE_SCHEMA+’.’+TABLE_NAME) AS Current_Identity,
    TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
    WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_SCHEMA+’.’+TABLE_NAME), ‘TableHasIdentity’) = 1
    AND TABLE_TYPE = ‘BASE TABLE’;

    Reply
  • Nice, that what exactly what I was looking for!!!

    Reply
  • IDENT_CURRENT does not tell if current identity value is null.

    Reply
    • Joshua Dobbelaar
      October 16, 2012 12:43 am

      True, IDENT_CURRENT will report 1 both when there are no records and when there is one with IDENTITY = 1. You can extend the above query to test for a row count of 0 and return 0 in that case, as in the sample below. (I borrowed code from our host’s 20100908 post – https://blog.sqlauthority.com/2010/09/08/sql-server-find-row-count-in-table-find-largest-table-in-database-part-2/ ) . Thanks, Pinal Dave!

      SELECT
      IDENT_SEED(IST.TABLE_SCHEMA + ‘.’ + IST.TABLE_NAME) AS Seed,
      IDENT_INCR(IST.TABLE_SCHEMA + ‘.’ + IST.TABLE_NAME) AS Increment,
      CASE Counts.RowCnt
      WHEN 0 THEN 0 ELSE IDENT_CURRENT(IST.TABLE_SCHEMA + ‘.’ + IST.TABLE_NAME) END AS Current_Identity,
      IST.TABLE_SCHEMA + ‘.’ + IST.TABLE_NAME AS [Schema.Table]
      FROM
      INFORMATION_SCHEMA.TABLES IST
      JOIN
      (
      SELECT
      sc.name +’.’+ ta.name TableName
      ,SUM(pa.rows) RowCnt
      FROM
      sys.tables ta
      INNER JOIN sys.partitions pa
      ON pa.OBJECT_ID = ta.OBJECT_ID
      INNER JOIN sys.schemas sc
      ON ta.schema_id = sc.schema_id
      WHERE
      ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
      GROUP BY
      sc.name,ta.name
      ) Counts ON Counts.TableName = IST.TABLE_SCHEMA + ‘.’ + IST.TABLE_NAME
      WHERE
      OBJECTPROPERTY(OBJECT_ID(IST.TABLE_SCHEMA + ‘.’ + IST.TABLE_NAME), ‘TableHasIdentity’) = 1
      AND IST.TABLE_TYPE = ‘BASE TABLE’

      Reply
  • This blog of yours never seizes to amaze me.

    Reply
  • Thank you – that was very enormously useful and has saved me a lot of pain.

    Reply
  • This is very enormously useful.

    Reply
  • To take this to the next level – Check the values against the upper limits of the data type used

    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

    Reply
  • Roberto Reynoso
    August 19, 2016 11:21 pm

    I really appreciated everything Pinal does for all of us. Thanks Pinal and all the contributors.

    Reply
  • Valentino Vranken
    May 20, 2019 1:46 pm

    Twelve years old and still useful, thanks Dave! :)

    I had to add schema_name for it to work in my environment:

    SELECT IDENT_SEED(TABLE_SCHEMA + ‘.’ + TABLE_NAME) AS Seed,
    IDENT_INCR(TABLE_SCHEMA + ‘.’ + TABLE_NAME) AS Increment,
    IDENT_CURRENT(TABLE_SCHEMA + ‘.’ + TABLE_NAME) AS Current_Identity,
    TABLE_SCHEMA, TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
    WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_SCHEMA + ‘.’ + TABLE_NAME), ‘TableHasIdentity’) = 1
    AND TABLE_TYPE = ‘BASE TABLE’

    Reply

Leave a Reply

Menu
Exit mobile version