清除表中的所有行，将身份规范重置为零并且不影响外键？ [英] Clear all the rows in a table resetting the identity specification back to zero and without affecting the foreign keys?

问题描述

我们已经创建了所有关系和依赖关系的数据库框架。但是在表中只是虚拟数据，我们需要摆脱这些虚拟数据，并开始添加正确的数据。我们如何清除所有内容，并将主键（IsIdentity：yes）恢复为零，也不会影响外表关系结构。

We have already created the database framework, with all the relations and dependencies. But inside the tables were just the dummy data, and we need to get rid of these dummy data, and start adding the correct ones. How can we clear everything and leave the primary keys (IsIdentity: yes) back to zero, and also without affecting the foreign-table relational structure.

非常感谢！ / p>

Thanks a lot!

推荐答案

您可以执行以下步骤：

-- disable all foreign key constraints
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

-- delete data in all tables
EXEC sp_MSForEachTable "DELETE FROM ?"

-- enable all constraints
exec sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

有关禁用约束和触发器的更多信息此处

More on disabling constraints and triggers here

如果某些表格具有标识列，我们可能需要重新设定种子。

if some of the tables have identity columns we may want to reseed them

EXEC sp_MSforeachtable "DBCC CHECKIDENT ( '?', RESEED, 0)"

b $ b

请注意，RESEED的行为在全新表格和之前插入了某个日期的表格之间有所不同： BOL ：

DBCC CHECKIDENT '，RESEED，newReseedValue）

当前标识值设置为
newReseedValue。如果没有行
插入到表中，因为
被创建，第一行在执行DBCC CHECKIDENT之后插入
将
使用newReseedValue作为标识。
否则，插入的下一行将
使用newReseedValue + 1.如果newReseedValue的值
小于标识列中的
最大值，
错误消息2627将在后续对表的引用时生成
。

The current identity value is set to the newReseedValue. If no rows have been inserted to the table since it was created, the first row inserted after executing DBCC CHECKIDENT will use newReseedValue as the identity. Otherwise, the next row inserted will use newReseedValue + 1. If the value of newReseedValue is less than the maximum value in the identity column, error message 2627 will be generated on subsequent references to the table.

这篇关于清除表中的所有行，将身份规范重置为零并且不影响外键？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！