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)
pinaldave
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..?