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 (http://blog.SQLAuthority.com)

About these ads

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

  1. 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

  2. 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..?

  3. 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’;

    • 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 – http://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’

  4. Pingback: SQL SERVER – Weekly Series – Memory Lane – #026 | SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s