IDENT_INCR (Transact-SQL)
Returns the increment value (returned as numeric (@@MAXPRECISION,0)) specified during the creation of an identity column in a table or view that has an identity column.
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_INCR may return NULL if the user does not have any permission on the object. For more information, see Metadata Visibility Configuration.
A. Returning the increment value for a specified table
The following example returns the increment value for the Person.Address table in the AdventureWorks database.
USE AdventureWorks2012;
GO
SELECT IDENT_INCR('Person.Address') AS Identity_Increment;
GO
USE AdventureWorks2012;
GO
SELECT IDENT_INCR('Person.Address') AS Identity_Increment;
GO
B. Returning the increment value from multiple tables
The following example returns the tables in the AdventureWorks database that include an identity column with an increment value.
USE AdventureWorks2012; GO SELECT TABLE_SCHEMA, TABLE_NAME, IDENT_INCR(TABLE_SCHEMA + '.' + TABLE_NAME) AS IDENT_INCR FROM INFORMATION_SCHEMA.TABLES WHERE IDENT_INCR(TABLE_SCHEMA + '.' + TABLE_NAME) IS NOT NULL;
USE AdventureWorks2012; GO SELECT TABLE_SCHEMA, TABLE_NAME, IDENT_INCR(TABLE_SCHEMA + '.' + TABLE_NAME) AS IDENT_INCR FROM INFORMATION_SCHEMA.TABLES WHERE IDENT_INCR(TABLE_SCHEMA + '.' + TABLE_NAME) IS NOT NULL;
Here is a partial result set.
TABLE_SCHEMA TABLE_NAME IDENT_INCR
------------ ------------------------ ----------
Person Address 1
Production ProductReview 1
Production TransactionHistory 1
Person AddressType 1
Production ProductSubcategory 1
Person vAdditionalContactInfo 1
dbo AWBuildVersion 1
Production BillOfMaterials 1
