Query that will provide details about each identity column within a database:
SELECT
DB_NAME() AS database_name,
schemas.name AS schema_name,
tables.name AS table_name,
columns.name AS column_name,
types.name AS type_name
FROM sys.tables
INNER JOIN sys.columns
ON tables.object_id = columns.object_id
INNER JOIN sys.types
ON types.user_type_id = columns.user_type_id
INNER JOIN sys.schemas
ON schemas.schema_id = tables.schema_id
WHERE columns.is_identity = 1;
Built-in function IDENT_CURRENT, which returns the last identity value that was allocated for a given identity column. This function accepts a table name (including the schema name) and returns the last identity value used:
SELECT IDENT_CURRENT('dbo.table1') AS current_identity_value;
Reseed the Identity Column
DBCC CHECKIDENT ('Dbo.Identity_Test', RESEED, 2147483646);
To insert value in identity colomn:-
set identity_insert table1 on
