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)
Is your SQL Server running slow and you want to speed it up without sharing server credentials? In my Comprehensive Database Performance Health Check, we can work together remotely and resolve your biggest performance troublemakers in less than 4 hours.
Once you learn my business secrets, you will fix the majority of problems in the future.
Have you ever opened any PowerPoint deck when you face SQL Server Performance Tuning emergencies? SQL Server Performance Tuning Practical Workshop is my MOST popular training with no PowerPoint presentations and 100% practical demonstrations.
Essentially I share my business secrets to optimize SQL Server performance.
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.
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
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’