重置SQL Server数据库中的列的标识

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.

我创建了一个应用程序，我使用了这段代码（我在Internet上找到）来清除所有数据并重置表的Identity列的标识。

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.

消息7997，级别16，状态1，行616“SupplierBalance”不包含标识列。

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

消息7997，级别16，状态1，行616'CustomerBalance'不包含标识列。

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.

谢谢。

2 个解决方案

#1

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

您可能希望使用TRUNCATE而不是使用DELETE并发出RESEED命令。

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:

使用TRUNCATE也比使用DELETE更有效。保罗怀特在答案中说：

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

是。由于多种原因，TRUNCATE TABLE效率更高：

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.

可能需要更少的锁。截断通常只需要在表级别进行单个模式修改锁定（并且每个扩展区域上的排除锁定）。删除可能会获得较低（行或页面）粒度的锁定以及取消分配的任何页面上的独占锁定。

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).

只有截断才能保证从堆表中释放所有页面。即使指定了排它表锁提示，删除也可能会在堆中留下空页（例如，如果为数据库启用了行版本控制隔离级别）。

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

截断始终记录最少（无论使用何种恢复模型）。只有页面解除分配操作记录在事务日志中。

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.

如果对象的大小为128个范围或更大，则截断可以使用延迟删除。延迟丢弃意味着实际的解除分配工作由后台服务器线程异步执行。

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

另外，我认为sp_MSforEachTable没有记录。你可以在这里找到另一种方法。

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:

由于某些表具有FK引用，因此在不删除约束的情况下不能使用TRUNCATE。但是你仍然可以使用DELETE。问题现在是RESEED，它在没有IDENTITY列的表上产生错误。要解决这个问题，这里有一个可以执行的动态SQL。当表格有IDENTITY列时，它只会重置：

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 ' + 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 CHECK CONSTRAINT ALL;'
FROM Cte

PRINT @sql;
EXEC sp_executesql @sql;

#2

1) 1st Argument Table Name

1）第一个参数表名

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

2）重置标识的第二个关键字（'RESEED'）

3) 3rd Next Identity value

3）第三个下一个身份值

  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

#1