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'
How to Read the Identity Column Values This Query Returns
The three functions answer three different questions. IDENT_SEED returns the value the table started with. IDENT_INCR returns the step between values. IDENT_CURRENT returns the last identity value generated for that table, by any session and in any scope.
That last point matters. IDENT_CURRENT is fine for a report like this one, but do not use it to find the row you just inserted, because another user may have inserted a row a moment later. For your own insert, use SCOPE_IDENTITY(), which returns the value created in your current scope.
Some things people notice when they run the query:
- Gaps in the numbers are normal. A rolled back or failed insert still uses up a value, and it is not given back.
- The current value can be higher than the largest value in the table, for the same reason.
- If the current value is close to the limit of the data type, like 2,147,483,647 for
INT, plan the move toBIGINTbefore inserts start to fail.
To check or fix one table, DBCC CHECKIDENT is the tool. With NORESEED it only reports the current value and the largest value in the column. With RESEED it changes the next value, so use it with care: it can create duplicates if nothing else keeps the values unique.
One small improvement: the query passes only TABLE_NAME to OBJECT_ID. Tables outside your default schema can be missed that way, so I put the schema in front, like TABLE_SCHEMA + '.' + TABLE_NAME. On SQL Server 2005 and later, sys.identity_columns also lists the seed, increment and last value for every identity column in one place.
When you reseed, a simple habit prevents surprises: run the report before and after, and write down both numbers. Also remember that TRUNCATE TABLE resets the identity back to its seed, while DELETE does not, so the next value after a big delete may be higher than you expect.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
20 Comments. Leave new
Da Vi ebem majkata na site sto nemat da go razberete ova !!!!
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
Thanks Pinal,
Was really hellpful in one my projects.. am under..
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.
How about if it’s not an identity column?
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..?
Use it like this:
DBCC CHECKIDENT( your_table_name, RESEED, new_value )
new_value = 0 so de identity starts fresh
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’;
very correct !
thank you Pinal Dave for the article, and Eric Russell for the fine tuning !
Nice, that what exactly what I was looking for!!!
IDENT_CURRENT does not tell if current identity value is null.
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’
This blog of yours never seizes to amaze me.
Thank you – that was very enormously useful and has saved me a lot of pain.
This is very enormously useful.
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
Amazing Script!
Is there a way to do this for every database in the instance?
I really appreciated everything Pinal does for all of us. Thanks Pinal and all the contributors.
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’