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://www.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?