IDENT_SEED (Transact-SQL)
Returns the original seed value (returned as numeric(@@MAXPRECISION,0)) that was specified when an identity column in a table or a view was created. Changing the current value of an identity column by using DBCC CHECKIDENT does not change the value returned by this function.
Returns NULL on error or if a caller does not have permission to view the object.
In SQL Server, a user can only view the metadata of securables that the user owns or on which the user has been granted permission. This means that metadata-emitting, built-in functions such as IDENT_SEED may return NULL if the user does not have any permission on the object. For more information, see Metadata Visibility Configuration.
A. Returning the seed value from a specified table
The following example returns the seed value for the Person.Address table in the AdventureWorks database.
USE AdventureWorks2012;
GO
SELECT IDENT_SEED('Person.Address') AS Identity_Seed;
GO
USE AdventureWorks2012;
GO
SELECT IDENT_SEED('Person.Address') AS Identity_Seed;
GO
B. Returning the seed value from multiple tables
The following example returns the tables in the AdventureWorks database that include an identity column with a seed value.
USE AdventureWorks2012; GO SELECT TABLE_SCHEMA, TABLE_NAME, IDENT_SEED(TABLE_SCHEMA + '.' + TABLE_NAME) AS IDENT_SEED FROM INFORMATION_SCHEMA.TABLES WHERE IDENT_SEED(TABLE_SCHEMA + '.' + TABLE_NAME) IS NOT NULL; GO
USE AdventureWorks2012; GO SELECT TABLE_SCHEMA, TABLE_NAME, IDENT_SEED(TABLE_SCHEMA + '.' + TABLE_NAME) AS IDENT_SEED FROM INFORMATION_SCHEMA.TABLES WHERE IDENT_SEED(TABLE_SCHEMA + '.' + TABLE_NAME) IS NOT NULL; GO
Here is a partial result set.
TABLE_SCHEMA TABLE_NAME IDENT_SEED
------------ --------------------------- -----------
Person Address 1
Production ProductReview 1
Production TransactionHistory 100000
Person AddressType 1
Production ProductSubcategory 1
Person vAdditionalContactInfo 1
dbo AWBuildVersion 1
