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.
IDENT_INCR ( 'table_or_view' )
' table_or_view '
Is an expression specifying the table or view to check for a valid identity increment value. table_or_view can be a character string constant enclosed in quotation marks, a variable, a function, or a column name. table_or_view is char, nchar, varchar, or nvarchar.
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.
The following example returns the increment value for the
Person.Address table in the AdventureWorks2012 database.
USE AdventureWorks2012; GO SELECT IDENT_INCR('Person.Address') AS Identity_Increment; GO
The following example returns the tables in the AdventureWorks2012 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;
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