Identity Column Details Findout in SQL Server 2008

These are two following queries using which you will get the identity column details in SQL Server.


select table_name + '.' + column_name, table_name, column_name, ordinal_position, data_type

from information_schema.columns

where

table_catalog = '<DB_NAME>'

and columnproperty(object_id(table_name), column_name,'IsIdentity') = 1

order by table_name


SELECT table_name

      ,column_name

      ,IDENT_CURRENT(table_name) as Identity_value

      ,IDENT_SEED(table_name) AS Seed

      ,IDENT_INCR(table_name) AS Increment

      ,IDENT_CURRENT(table_name) [Current_Value]

      ,data_type

 FROM information_schema.columns

WHERE COLUMNPROPERTY(OBJECT_ID(table_name),column_name,'IsIdentity')=1

ORDER BY table_name