Reset the identity of a column in SQL Server database

Question

I have created an application and I have used this piece of code (which I have found on the Internet) to clear all the data and reset the Identity of the Identity columns of the tables.

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' 
GO 

EXEC sp_MSForEachTable 'DELETE FROM ?' 
GO 

EXEC sp_MSForEachTable 'DBCC CHECKIDENT(''?'', RESEED, 0)'
GO

EXEC sp_MSForEachTable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL' 
GO

But I have two tables which do not have Identity columns and that gives me a problem saying:

Msg 7997, Level 16, State 1, Line 616
'SupplierBalance' does not contain an identity column.

Msg 7997, Level 16, State 1, Line 616
'CustomerBalance' does not contain an identity column.

I want to know if there is a way to reset the identity of the columns of the tables, which only have identity columns without getting an error.

Thank you.

Answer 1

Instead of using DELETE and issuing a RESEED command, you may want to use TRUNCATE instead.

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' 
GO 

EXEC sp_MSForEachTable 'TRUNCATE TABLE ?' 
GO 

EXEC sp_MSForEachTable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL' 
GO

Using TRUNCATE is also more efficient than using DELETE. According to Paul White in his answer here:

Yes. TRUNCATE TABLE is more efficient for a number of reasons:

1. Fewer locks may be needed. Truncation typically requires only a single schema modification lock at the table level (and exclusive locks on each extent deallocated). Deletion might acquire locks at a lower (row or page) granularity as well as exclusive locks on any pages deallocated.

2. Only truncation guarantees that all pages are deallocated from a heap table. Deletion may leave empty pages in a heap even if an exclusive table lock hint is specified (for example if a row-versioning isolation level is enabled for the database).

3. Truncation is always minimally logged (regardless of the recovery model in use). Only page deallocation operations are recorded in the transaction log.

4. Truncation can use deferred drop if the object is 128 extents or larger in size. Deferred drop means the actual deallocation work is performed asynchronously by a background server thread.

Additionally, I beleve sp_MSforEachTable is undocumented. You can find an alternative method here.

---

Since some of your tables have FK reference, you cannot use TRUNCATE without dropping the constraints. But still you can use DELETE. The problem now is with RESEED, that it produces an error on tables without IDENTITY column. To solve that, here is a dynamic SQL you can execute. It'll only RESEED when the table has an IDENTITY column:

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'SET NOCOUNT ON;';

WITH Cte(tableName, hasIdentity) AS(
    SELECT t.name, CAST(ISNULL(ic.object_id, 0) AS BIT)
    FROM sys.tables t
    LEFT JOIN sys.identity_columns ic
        ON t.object_id = ic.object_id
    WHERE t.type = 'U'
)
SELECT @sql = @sql + CHAR(10) + 
    N'ALTER TABLE ' + QUOTENAME(tableName) + ' NOCHECK CONSTRAINT ALL;' + CHAR(10) +
    N'DELETE FROM ' + QUOTENAME(tableName) + ';' + CHAR(10) + 
    CASE
        WHEN hasIdentity = 1 THEN 
            N'DBCC CHECKIDENT(''' + QUOTENAME(tableName) + ''', RESEED, 0) WITH NO_INFOMSGS;' + CHAR(10)
        ELSE ''
    END +
    N'ALTER TABLE ' + QUOTENAME(tableName) + ' WITH CHECK CONSTRAINT ALL;'
FROM Cte

PRINT @sql;
EXEC sp_executesql @sql;

Answer 2

1) 1st Argument Table Name

2) 2nd Keyword ('RESEED') for Reset Identity

3) 3rd Next Identity value

  DBCC CHECKIDENT ('ResetIDClmn', RESEED, 5)

Example

if OBJECT_ID('ResetIDClmn') is not null
Begin
Drop table ResetIDClmn
 End
        Create table ResetIDClmn (P_Id int identity,abc varchar(10),PRIMARY KEY (P_Id))
        Insert Into ResetIDClmn (abc) values ('sdfsfd')
        Insert Into ResetIDClmn (abc) values ('sdfsfd')
        Insert Into ResetIDClmn (abc) values ('sdfsfd')
        Insert Into ResetIDClmn (abc) values ('sdfsfd')
        Insert Into ResetIDClmn (abc) values ('sdfsfd')
        Insert Into ResetIDClmn (abc) values ('sdfsfd')
        Insert Into ResetIDClmn (abc) values ('sdfsfd')
        Insert Into ResetIDClmn (abc) values ('sdfsfd')
        Insert Into ResetIDClmn (abc) values ('sdfsfd')
        Insert Into ResetIDClmn (abc) values ('sdfsfd')
        Insert Into ResetIDClmn (abc) values ('sdfsfd')
        Insert Into ResetIDClmn (abc) values ('sdfsfd')

        Select * from ResetIDClmn

        --truncate table ResetIDClmn 

        delete ResetIDClmn where P_Id>5

        DBCC CHECKIDENT ('ResetIDClmn', RESEED, 5)
        Insert Into ResetIDClmn (abc) values ('sdfsfd')
        Insert Into ResetIDClmn (abc) values ('sdfsfd')
        Select * from ResetIDClmn