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