Edit

IDENT_SEED (Transact-SQL)

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance SQL database in Microsoft Fabric

The IDENT_SEED system function returns the original seed value specified when creating an identity column in a table or a view. Changing the current value of an identity column by using DBCC CHECKIDENT doesn't change the value returned by this function.

Transact-SQL syntax conventions

Syntax

IDENT_SEED ( 'table_or_view' )

Arguments

table_or_view

An expression that specifies the table or view to check for an 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.

Return types

numeric(@@MAXPRECISION,0))

Exceptions

Returns NULL on error or if a caller doesn't have permission to view the object.

In SQL Server, a user can only view the metadata of securables that the user either owns or is granted permission on. This security means that metadata-emitting, built-in functions such as IDENT_SEED might return NULL if the user doesn't have any permission on the object. For more information, see Metadata Visibility Configuration.

Remarks

The IDENT_SEED function is not available for identity columns in Fabric Data Warehouse.

Examples

A. Return the seed value from a specified table

The following example returns the seed value for the Person.Address table in the AdventureWorks2025 database.

USE AdventureWorks2022;  
GO  
SELECT IDENT_SEED('Person.Address') AS Identity_Seed;  
GO

B. Return the seed value from multiple tables

The following example returns the tables in the AdventureWorks2025 database with an identity column with a seed value.

USE AdventureWorks2022;  
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

Related content

  • Last updated on