How to check the current identiy value in a table ?

"DBCC CHECKIDENT" Statement is used to check the current identity value for the specified table and if requires then can modify/change the identity value on a identity column.

Syntax:
DBCC CHECKIDENT ('Table_Name' [ , { NORESEED  | { RESEED [ , new_reseed_value ] } } ] )

Example:

CREATE TABLE Product(ProductID int identity(1,1), ProductName varchar(20), Price decimal(18,6) )

INSERT INTO Product VALUES ('Book',150.10),('Pen',35.50)

-- To check the current maximum identity value in the column, execute the following statement


DBCC CHECKIDENT ('Product', NORESEED)