Export (0) Print
Expand All
This topic has not yet been rated - Rate this topic

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.

Topic link icon Transact-SQL Syntax Conventions

IDENT_SEED ( 'table_or_view' )
' table_or_view '

Is an expression that specifies the table or view to check for a identity seed 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_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

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.