如何删除或截断 SQL Server 中的表？

How to delete or truncate table in SQL Server?

提问by Jango

I need to clear many tables (preferably truncate table). But tables have many FK constraints. I tried something like this, but failed:-

我需要清除许多表（最好是截断表）。但是表有很多 FK 约束。我尝试过这样的事情，但失败了：-

ALTER TABLE Table1 NOCHECK CONSTRAINT ALL 
TRUNCATE TABLE Table1
ALTER TABLE Table1 WITH CHECK CHECK CONSTRAINT ALL

This is the error i am getting:-

这是我得到的错误：-

Cannot truncate table 'Test' because it is being referenced by a FOREIGN KEY constraint.

无法截断表“Test”，因为它正被 FOREIGN KEY 约束引用。

Please suggest me how to delete or truncate table by dropping constraints temporarily.

请建议我如何通过暂时删除约束来删除或截断表。

回答by KM.

just delete them in the proper FK order:

只需按照正确的 FK 顺序删除它们：

DELETE GreatGrandChild
DELETE Child
DELETE Parent

and don't worry about dropping constraints.

并且不要担心删除约束。

sample code:

示例代码：

create table ParentTable (ParentID int primary key not null, RowValue varchar(10))
INSERT INTO ParentTable VALUES (1,'AAA')
INSERT INTO ParentTable VALUES (2,'BBB')

create table ChildTable (ChildID int primary key not null, ParentID int, RowValue varchar(10))

ALTER TABLE ChildTable ADD CONSTRAINT FK_ChildTable_ParentTable FOREIGN KEY
 (ParentID) REFERENCES dbo.ParentTable (ParentID) ON UPDATE  NO ACTION  ON DELETE  NO ACTION 

INSERT INTO ChildTable VALUES (10,1,'a')
INSERT INTO ChildTable VALUES (11,1,'aa')
INSERT INTO ChildTable VALUES (12,2,'b')
INSERT INTO ChildTable VALUES (13,1,'aaa')

DELETE ChildTable
DELETE ParentTable

to find the tables that depend on your table run this query:

要查找依赖于您的表的表，请运行以下查询：

select 
    object_name(parent_object_id) AS ReferencesYourTable
        ,object_name(referenced_object_id) AS YourTable
        ,* 
    from sys.foreign_keys 
    WHERE object_name(referenced_object_id)='YourTable'

for the above query, delete all the rows in each table listed prior to deleting YourTable.

对于上述查询，在删除 YourTable 之前删除列出的每个表中的所有行。

回答by Pondlife

Contrary to what others have posted, you can never truncate a table referenced by a foreign key. It's documented in Books Online under TRUNCATE TABLE, but trying it out yourself is a lot faster:

与其他人发布的内容相反，您永远不能截断外键引用的表。它记录在联机丛书中的 TRUNCATE TABLE 下，但自己尝试一下要快得多：

create table Parent (col1 int primary key)

create table Child (
    col1 int primary key, 
    col2 int, 
    constraint fk foreign key (col2) references Parent (col1)
)


-- works
truncate table Child
-- doesn't work
truncate table Parent

alter table child nocheck constraint all

-- still doesn't work, even though the FK is disabled
truncate table Parent

drop table Child
drop table Parent

The (conceptual) reason it doesn't work is that TRUNCATE is a physical operation, not a logical one. So it is not 'foreign key aware' and if you let it ignore foreign keys it would kill referential integrity.

它不起作用的（概念上的）原因是 TRUNCATE 是一种物理操作，而不是逻辑操作。所以它不是“外键感知”，如果你让它忽略外键，它会破坏参照完整性。

The usual solutions (as mentioned by others) are:

通常的解决方案（如其他人提到的）是：

Solution 1

解决方案1

1. Drop foreign keys
2. Truncate tables
3. Re-create foreign keys

1. 删除外键
2. 截断表
3. 重新创建外键

Solution 2

解决方案2

1. Drop tables
2. Re-create tables

1. 删除表
2. 重新创建表

Either solution works, it's really a deployment issue as to which is easier and suits your situation better. I know you said it's a one-time task, but I would still script it, even if only as a learning experience. Solution 1 is easy in pure TSQL; solution 2 is easier using an external language.

任何一种解决方案都有效，这实际上是一个部署问题，哪个更容易并且更适合您的情况。我知道你说过这是一项一次性任务，但我仍然会编写它，即使只是作为一种学习经验。解决方案 1 在纯 TSQL 中很容易；解决方案 2 使用外部语言更容易。

回答by Zain Ali

Also try this.

也试试这个。

Uncheck constraints temporarily and then recheck.

暂时取消检查约束，然后重新检查。

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'    
EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL'    
EXEC sp_MSForEachTable 'DELETE FROM ?'   
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'    
EXEC sp_MSForEachTable 'ALTER TABLE ? ENABLE TRIGGER ALL'

回答by abdelwahed

use the following command after deletion of all rows in that table by using delete statement

使用 delete 语句删除该表中的所有行后使用以下命令

delete * from tablename

从表名中删除 *

DBCC CHECKIDENT (tablename, RESEED, 0)

DBCC CHECKIDENT（表名，RESEED，0）

回答by Achilles

The simpliest way to do this is to delete the constraint(s) then reapply them after the truncation.

最简单的方法是删除约束，然后在截断后重新应用它们。

回答by Michael Pakhantsov

You need also drop constraints in all your tables which referenced to your truncated table. After that you will be able truncate table. But do not forget create them again.

您还需要删除所有引用截断表的表中的约束。之后，您将能够截断表。但不要忘记再次创建它们。

Also MSSQL does not allow truncate table even if all tables referenced to your truncated table have not rows.

即使所有引用到截断表的表都没有行，MSSQL 也不允许截断表。

So you need drop FK constraints firstly.

所以你首先需要删除 FK 约束。

回答by AllenG

What you need to do (and there may be a tool, but I don't know of one) is dissable ALL of your relationships which attach to your affected tables (both to and from the table being truncated). That often means disabling constraints on other tables.

您需要做的（可能有一个工具，但我不知道有一个）是禁用所有附加到受影响表的关系（与被截断的表之间的关系）。这通常意味着禁用对其他表的约束。

<SoapBox>I'm sure you're aware of this, but I'd be remiss if I didn't point out that those constraints probably exist for a verygood reason, and you need to be very, very sure that your data is clean both before and after your truncate.<\SoapBox>

<SoapBox>我敢肯定，你意识到这一点，但我会失职，如果我没有指出，这些限制可能存在一个 非常充分的理由，你需要非常，非常肯定你的数据是干净的两在截断之前和之后。<\SoapBox>

回答by Frank

The easiest (maybe not the fastest) way would be to DELETE FROM Table1.

最简单（可能不是最快）的方法是 DELETE FROM Table1.

That does work even wit foreign keys (however, the order of deletes should take into account to delete tables with foreign keys before tables with the matching primary keys.

这甚至适用于外键（但是，删除的顺序应该考虑在具有匹配主键的表之前删除具有外键的表。

回答by Vidar Nordnes

Try this:

尝试这个：

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

回答by kniemczak

You would need to delete or truncate any table with a foreign key to Table1 or perform a cascading delete if your database supports it (I think it was added in SQL Server 2005)

如果您的数据库支持，您需要删除或截断任何带有 Table1 外键的表或执行级联删除（我认为它是在 SQL Server 2005 中添加的）

Sql Server Cascading Delete

Sql Server 级联删除