@denver_citizen和@Peter Szanto的回答对我不太适用，但我修改了它们，以解释:

组合键 “删除”和“更新”动作 重新添加时检查索引 dbo以外的模式 同时处理多个表

DECLARE @Debug bit = 0;

-- List of tables to truncate
select
    SchemaName, Name
into #tables
from (values 
    ('schema', 'table')
    ,('schema2', 'table2')
) as X(SchemaName, Name)


BEGIN TRANSACTION TruncateTrans;

with foreignKeys AS (
     SELECT 
        SCHEMA_NAME(fk.schema_id) as SchemaName
        ,fk.Name as ConstraintName
        ,OBJECT_NAME(fk.parent_object_id) as TableName
        ,SCHEMA_NAME(t.SCHEMA_ID) as ReferencedSchemaName
        ,OBJECT_NAME(fk.referenced_object_id) as ReferencedTableName
        ,fc.constraint_column_id
        ,COL_NAME(fk.parent_object_id, fc.parent_column_id) AS ColumnName
        ,COL_NAME(fk.referenced_object_id, fc.referenced_column_id) as ReferencedColumnName
        ,fk.delete_referential_action_desc
        ,fk.update_referential_action_desc
    FROM sys.foreign_keys AS fk
        JOIN sys.foreign_key_columns AS fc
            ON fk.object_id = fc.constraint_object_id
        JOIN #tables tbl 
            ON OBJECT_NAME(fc.referenced_object_id) = tbl.Name
        JOIN sys.tables t on OBJECT_NAME(t.object_id) = tbl.Name 
            and SCHEMA_NAME(t.schema_id) = tbl.SchemaName
            and t.OBJECT_ID = fc.referenced_object_id
)



select
    quotename(fk.ConstraintName) AS ConstraintName
    ,quotename(fk.SchemaName) + '.' + quotename(fk.TableName) AS TableName
    ,quotename(fk.ReferencedSchemaName) + '.' + quotename(fk.ReferencedTableName) AS ReferencedTableName
    ,replace(fk.delete_referential_action_desc, '_', ' ') AS DeleteAction
    ,replace(fk.update_referential_action_desc, '_', ' ') AS UpdateAction
    ,STUFF((
        SELECT ',' + quotename(fk2.ColumnName)
        FROM foreignKeys fk2 
        WHERE fk2.ConstraintName = fk.ConstraintName and fk2.SchemaName = fk.SchemaName
        ORDER BY fk2.constraint_column_id
        FOR XML PATH('')
    ),1,1,'') AS ColumnNames
    ,STUFF((
        SELECT ',' + quotename(fk2.ReferencedColumnName)
        FROM foreignKeys fk2 
        WHERE fk2.ConstraintName = fk.ConstraintName and fk2.SchemaName = fk.SchemaName
        ORDER BY fk2.constraint_column_id
        FOR XML PATH('')
    ),1,1,'') AS ReferencedColumnNames
into #FKs
from foreignKeys fk
GROUP BY fk.SchemaName, fk.ConstraintName, fk.TableName, fk.ReferencedSchemaName, fk.ReferencedTableName, fk.delete_referential_action_desc, fk.update_referential_action_desc



-- Drop FKs
select 
    identity(int,1,1) as ID,
    'ALTER TABLE ' + fk.TableName + ' DROP CONSTRAINT ' + fk.ConstraintName AS script
into #scripts
from #FKs fk

-- Truncate 
insert into #scripts
select distinct 
    'TRUNCATE TABLE ' + quotename(tbl.SchemaName) + '.' + quotename(tbl.Name) AS script
from #tables tbl

-- Recreate
insert into #scripts
select 
    'ALTER TABLE ' + fk.TableName + 
    ' WITH CHECK ADD CONSTRAINT ' + fk.ConstraintName + 
    ' FOREIGN KEY ('+ fk.ColumnNames +')' + 
    ' REFERENCES ' + fk.ReferencedTableName +' ('+ fk.ReferencedColumnNames +')' +
    ' ON DELETE ' + fk.DeleteAction COLLATE Latin1_General_CI_AS_KS_WS + ' ON UPDATE ' + fk.UpdateAction COLLATE Latin1_General_CI_AS_KS_WS AS script
from #FKs fk


DECLARE @script nvarchar(MAX);

DECLARE curScripts CURSOR FOR 
    select script
    from #scripts
    order by ID

OPEN curScripts

WHILE 1=1 BEGIN
    FETCH NEXT FROM curScripts INTO @script
    IF @@FETCH_STATUS != 0 BREAK;

    print @script;
    IF @Debug = 0
        EXEC (@script);
END
CLOSE curScripts
DEALLOCATE curScripts


drop table #scripts
drop table #FKs
drop table #tables


COMMIT TRANSACTION TruncateTrans;

如果我理解正确的话，您要做的是为涉及集成测试的DB设置一个干净的环境。

我在这里的方法是放弃整个模式，稍后再重新创建它。

原因:

您可能已经有了“创建模式”脚本。重用它进行测试隔离很容易。 创建模式非常快。 使用这种方法，可以很容易地设置脚本，让每个fixture创建一个NEW模式(使用临时名称)，然后开始并行运行测试fixture，使测试套件中最慢的部分更快。

你可以按照这个步骤来做， 通过重新播种表，可以删除表中的数据。

delete from table_name
dbcc checkident('table_name',reseed,0)

如果出现错误，则必须重新播种主表。

我写了以下方法，并尝试将它们参数化，这样你就可以在查询文档中运行它们，或者轻松地使用它们制作一个有用的SP。

一)删除

如果你的表没有数百万条记录，这工作得很好，没有任何Alter命令:

---------------------------------------------------------------
------------------- Just Fill Parameters Value ----------------
---------------------------------------------------------------
DECLARE @DbName AS NVARCHAR(30) = 'MyDb'         --< Db Name
DECLARE @Schema AS NVARCHAR(30) = 'dbo'          --< Schema
DECLARE @TableName AS NVARCHAR(30) = 'Book'      --< Table Name
------------------ /Just Fill Parameters Value ----------------

DECLARE @Query AS NVARCHAR(500) = 'Delete FROM ' + @TableName

EXECUTE sp_executesql @Query
SET @Query=@DbName+'.'+@Schema+'.'+@TableName
DBCC CHECKIDENT (@Query,RESEED, 0)

在我上面的回答中，解决问题中提到的问题的方法是基于@s15199d的回答。

B)截断

如果你的表有数百万条记录，或者你在你的代码中没有任何Alter命令的问题，那么使用这个:

--   Book                               Student
--
--   |  BookId  | Field1 |              | StudentId |  BookId  |
--   ---------------------              ------------------------ 
--   |    1     |    A   |              |     2     |    1     |  
--   |    2     |    B   |              |     1     |    1     |
--   |    3     |    C   |              |     2     |    3     |  

---------------------------------------------------------------
------------------- Just Fill Parameters Value ----------------
---------------------------------------------------------------
DECLARE @DbName AS NVARCHAR(30) = 'MyDb'
DECLARE @Schema AS NVARCHAR(30) = 'dbo'
DECLARE @TableName_ToTruncate AS NVARCHAR(30) = 'Book'

DECLARE @TableName_OfOwnerOfConstraint AS NVARCHAR(30) = 'Student' --< Decelations About FK_Book_Constraint
DECLARE @Ref_ColumnName_In_TableName_ToTruncate AS NVARCHAR(30) = 'BookId' --< Decelations About FK_Book_Constraint
DECLARE @FK_ColumnName_In_TableOfOwnerOfConstraint AS NVARCHAR(30) = 'Fk_BookId' --< Decelations About FK_Book_Constraint
DECLARE @FK_ConstraintName AS NVARCHAR(30) = 'FK_Book_Constraint'                --< Decelations About FK_Book_Constraint
------------------ /Just Fill Parameters Value ----------------

DECLARE @Query AS NVARCHAR(2000)

SET @Query= 'ALTER TABLE '+@TableName_OfOwnerOfConstraint+' DROP CONSTRAINT '+@FK_ConstraintName
EXECUTE sp_executesql @Query

SET @Query= 'Truncate Table '+ @TableName_ToTruncate
EXECUTE sp_executesql @Query

SET @Query= 'ALTER TABLE '+@TableName_OfOwnerOfConstraint+' ADD CONSTRAINT '+@FK_ConstraintName+' FOREIGN KEY('+@FK_ColumnName_In_TableOfOwnerOfConstraint+') REFERENCES '+@TableName_ToTruncate+'('+@Ref_ColumnName_In_TableName_ToTruncate+')'
EXECUTE sp_executesql @Query

在上面的回答中，我解决问题中提到的问题的方法是基于@LauroWolffValenteSobrinho的回答。 如果你有不止一个约束，那么你应该把它的代码像我一样附加到上面的查询 此外，您还可以更改上面的代码基@SerjSagan answer来禁用或启用约束

好吧，因为我没有找到我使用的非常简单的解决方案的例子，这是:

删除外键; 截断表 重新创建外键

是这样的:

1)查找导致失败的外键名称(例如:FK_PROBLEM_REASON，带字段ID，来自表TABLE_OWNING_CONSTRAINT) 2)从表中删除该键:

ALTER TABLE TABLE_OWNING_CONSTRAINT DROP CONSTRAINT FK_PROBLEM_REASON

3)截断招聘表

TRUNCATE TABLE TABLE_TO_TRUNCATE

4)重新添加第一个表的键:

ALTER TABLE TABLE_OWNING_CONSTRAINT ADD CONSTRAINT FK_PROBLEM_REASON FOREIGN KEY(ID) REFERENCES TABLE_TO_TRUNCATE (ID)

就是这样。

DELETE FROM TABLENAME
DBCC CHECKIDENT ('DATABASENAME.dbo.TABLENAME', RESEED, 0)

注意，如果您有数百万条以上的记录，这可能不是您想要的，因为它非常慢。