SQL Server DBCC CHECKIDENT: Bulk Identity Column Analysis

calendar Aug 3, 2025 / · 8 min read · sql server database administration identity columns maintenance scripts dbcc commands sql scripts dbcc checkident sysobjects sys.tables sys.schemas sys.identity_columns  ·
Share on: linkedin copy

DBCC CHECKIDENT with NORESEED reports the current identity value of a table next to the largest value actually stored in the identity column, without changing either. It works on one table per call, so checking a whole database means writing one command per table. The script below generates those commands from the catalog views, limited to tables that really have an identity column, and adds a read-only inventory of seed, increment and last value.

Purpose and Overview

sys.identity_columns is the catalog view that lists every identity column in a database. It returns every column of sys.columns for those columns and adds seed_value, increment_value, last_value and is_not_for_replication. Joined to sys.tables and sys.schemas, it gives the exact set of tables DBCC CHECKIDENT can be run against, which is the part a hand-written list always gets wrong over time.

The command itself requires that the table contain an identity column, so a generator that simply emits one statement per user table produces errors for every table without one. Filtering through sys.identity_columns removes those. The generator also schema-qualifies and delimits each name, because the command needs delimiters on two-part names such as [Person].[AddressType], and a name that needs delimiters would otherwise fail.

The usual reason to run this is a mismatch between the identity counter and the data: rows deleted from the table, a manual RESEED, or inserts that failed or were rolled back, since a consumed identity value is never handed out again. NORESEED shows the two numbers side by side. If they differ, the identity value can be reset to avoid errors or gaps in the sequence. This script only reports; deciding whether to reseed stays a manual step.

Code Breakdown

The script has three parts: a single-table check, the command generator, and the identity inventory.

 1-- 1. Check one table without changing anything
 2DBCC CHECKIDENT ('dbo.Jobs', NORESEED);
 3
 4-- 2. Generate a NORESEED check for every user table that has an identity column
 5SELECT
 6    'DBCC CHECKIDENT (''' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ''', NORESEED);' AS check_command
 7FROM sys.tables AS t
 8INNER JOIN sys.schemas AS s
 9    ON s.schema_id = t.schema_id
10WHERE EXISTS (
11    SELECT 1
12    FROM sys.identity_columns AS ic
13    WHERE ic.object_id = t.object_id
14)
15ORDER BY s.name, t.name;
16
17-- 3. Identity inventory: type, seed, increment, last value and current identity
18SELECT
19    s.name                       AS schema_name,
20    t.name                       AS table_name,
21    ic.name                      AS column_name,
22    TYPE_NAME(ic.user_type_id)   AS data_type,
23    ic.seed_value,
24    ic.increment_value,
25    ic.last_value,
26    IDENT_CURRENT(QUOTENAME(s.name) + '.' + QUOTENAME(t.name)) AS current_identity
27FROM sys.identity_columns AS ic
28INNER JOIN sys.tables AS t
29    ON t.object_id = ic.object_id
30INNER JOIN sys.schemas AS s
31    ON s.schema_id = t.schema_id
32ORDER BY s.name, t.name;

Checking a single table

DBCC CHECKIDENT (table_name, NORESEED) specifies that the current identity value should not be changed. It returns the current identity value and the current maximum value of the identity column. The message has this shape:

1Checking identity information: current identity value '<current identity value>', current column value '<current column value>'.
2DBCC execution completed. If DBCC printed error messages, contact your system administrator.

If the two numbers are not the same, the identity value can be reset to avoid errors or gaps in the sequence. The caller must own the schema that contains the table, or be a member of sysadmin, db_owner or db_ddladmin. WITH NO_INFOMSGS suppresses the informational messages.

The same command without NORESEED is not a read-only check. DBCC CHECKIDENT (table_name) and DBCC CHECKIDENT (table_name, RESEED) reset the identity value to the maximum value in the column when the current identity value is lower than that maximum. DBCC CHECKIDENT (table_name, RESEED, new_reseed_value) sets the current identity value to whatever is given. That is why the generator hard-codes NORESEED.

Generating the commands

The generator reads sys.tables, which returns a row for each user table, so no type = 'U' filter is needed. sys.schemas supplies the schema name through schema_id. The EXISTS test against sys.identity_columns keeps only tables with an identity column. QUOTENAME wraps each name in brackets and doubles any closing bracket inside the name, so unusual names still produce a valid delimited identifier.

The output is one DBCC CHECKIDENT statement per row, ending in a semicolon. Copy the result column into a new query window and run it. Each statement returns its own message, so on a database with many identity tables the messages tab is the result.

An older form of this script reads sysobjects with type = 'U'. sysobjects is a SQL Server 2000 system table kept as a view for backward compatibility, and it is marked for removal in a future version, with a recommendation to avoid it in new work. It also returns every user table, identity or not, which is a further reason the generator above works from the current views.

The identity inventory query

sys.identity_columns supplies seed_value, increment_value and last_value. All three are sql_variant values of the same data type as the column itself. last_value is the last value generated for the column. TYPE_NAME(ic.user_type_id) turns the inherited user_type_id into a type name, which matters for the next check: the type decides how much room is left.

IDENT_CURRENT returns the last identity value generated for the table in any session and any scope. When the table has never contained rows, or has been truncated, it returns the seed value. Failed statements and rolled-back transactions can change the current identity of a table and leave gaps, because the identity value is never rolled back. For that reason IDENT_CURRENT should not be used to predict the next value: other sessions may insert in between, so the next value can differ from IDENT_CURRENT plus the increment.

Key Benefits and Use Cases

  • Complete coverage - the command list comes from the catalog, so a table added last week is included without anyone editing a list.
  • No false errors - only tables with an identity column are listed, so the run does not fail on tables the command cannot check.
  • Safe to run - NORESEED leaves the identity value unchanged, so the generated batch can be run against production.
  • Mismatch detection - the two numbers in the NORESEED message show whether the identity value and the column maximum still agree after deletes, reseeds or failed inserts.
  • Overflow watch - the data type, seed, increment and last value in the inventory show how much of an int or smallint range has been consumed.
  • Before-and-after comparison - saving the inventory before and after a large delete or a manual reseed shows exactly what moved.

Performance Considerations

  • Permissions for the DBCC calls: the caller must own the schema that contains the table, or be a member of sysadmin, db_owner or db_ddladmin, so a generated batch run under a weaker login fails table by table.
  • Output volume: every generated statement returns its own result message, so the output grows with the number of identity tables.
  • Permissions limit what is seen: visibility of metadata in catalog views is limited to securables that the user owns or has been granted some permission on, and IDENT_CURRENT returns NULL when the caller has no permission to view the object. A low-privilege login can produce a short or empty list without any error.
  • Run it per database: the views are database-scoped, so the script has to be run in each database being checked, or wrapped in a loop over databases.
  • Pair with a reseed only after reading the result: RESEED with a value lower than the column maximum raises error 2627 on later inserts when a primary key or unique constraint exists on the column, and produces duplicate identity values when none does.

Practical Tips

  • Save the output of the first run as a baseline. A later run that shows a different gap between the two numbers is the signal worth investigating.
  • Check the data type column in the inventory first. An identity column on int stops at 2,147,483,647, and smallint at 32,767. bigint reaches 9,223,372,036,854,775,807. tinyint stops at 255.
  • Do not treat gaps as corruption. The identity property does not guarantee consecutive values: values can be lost after a server restart or failure because SQL Server might cache identity values, and a rolled-back insert consumes a value that is never generated again.
  • Do not rely on identity for uniqueness. Uniqueness has to be enforced by a PRIMARY KEY or UNIQUE constraint or a unique index.
  • When the check shows a current identity value larger than the column maximum, the command does not reset it on its own. Reseed explicitly to the column maximum, or reseed to a low value and then run RESEED with no value to correct it.
  • After deleting every row from a table, RESEED with the new starting value is the way to reset the identity value.
  • To check one table in a non-default schema, pass the delimited two-part name in quotes, for example 'Person.AddressType' or '[Person].[AddressType]'.

Conclusion

DBCC CHECKIDENT with NORESEED answers one question per table: does the identity counter agree with the data? Driving it from sys.tables, sys.schemas and sys.identity_columns makes that question cover the whole database without a hand-kept list, and the inventory query adds the type and last value that show how close a column is to its limit. The script reports only; any reseed is a separate, deliberate command.

References

Posts in this series