我写了以下方法，并尝试将它们参数化，这样你就可以在查询文档中运行它们，或者轻松地使用它们制作一个有用的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来禁用或启用约束

我刚刚发现，只要先禁用子表上的外键约束，就可以在父表上使用TRUNCATE表。 如。

子表上的外键约束child_par_ref引用PARENT_TABLE

ALTER TABLE CHILD_TABLE DISABLE CONSTRAINT child_par_ref;
TRUNCATE TABLE CHILD_TABLE;
TRUNCATE TABLE PARENT_TABLE;
ALTER TABLE CHILD_TABLE ENABLE CONSTRAINT child_par_ref;

如果不删除约束，就不能截断表。disable也不行。你需要放下一切。我已经做了一个脚本，删除所有约束，然后重新创建。

请确保将其包装在事务中;)

SET NOCOUNT ON
GO

DECLARE @table TABLE(
RowId INT PRIMARY KEY IDENTITY(1, 1),
ForeignKeyConstraintName NVARCHAR(200),
ForeignKeyConstraintTableSchema NVARCHAR(200),
ForeignKeyConstraintTableName NVARCHAR(200),
ForeignKeyConstraintColumnName NVARCHAR(200),
PrimaryKeyConstraintName NVARCHAR(200),
PrimaryKeyConstraintTableSchema NVARCHAR(200),
PrimaryKeyConstraintTableName NVARCHAR(200),
PrimaryKeyConstraintColumnName NVARCHAR(200)
)

INSERT INTO @table(ForeignKeyConstraintName, ForeignKeyConstraintTableSchema, ForeignKeyConstraintTableName, ForeignKeyConstraintColumnName)
SELECT
U.CONSTRAINT_NAME,
U.TABLE_SCHEMA,
U.TABLE_NAME,
U.COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
ON U.CONSTRAINT_NAME = C.CONSTRAINT_NAME
WHERE
C.CONSTRAINT_TYPE = 'FOREIGN KEY'

UPDATE @table SET
PrimaryKeyConstraintName = UNIQUE_CONSTRAINT_NAME
FROM
@table T
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R
ON T.ForeignKeyConstraintName = R.CONSTRAINT_NAME

UPDATE @table SET
PrimaryKeyConstraintTableSchema = TABLE_SCHEMA,
PrimaryKeyConstraintTableName = TABLE_NAME
FROM @table T
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
ON T.PrimaryKeyConstraintName = C.CONSTRAINT_NAME

UPDATE @table SET
PrimaryKeyConstraintColumnName = COLUMN_NAME
FROM @table T
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
ON T.PrimaryKeyConstraintName = U.CONSTRAINT_NAME

--DROP CONSTRAINT:

DECLARE @dynSQL varchar(MAX);

DECLARE cur CURSOR FOR
SELECT
'
ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' + ForeignKeyConstraintTableName + ']
DROP CONSTRAINT ' + ForeignKeyConstraintName + '
'
FROM
@table

OPEN cur

FETCH cur into @dynSQL
WHILE @@FETCH_STATUS = 0 
BEGIN
    exec(@dynSQL)
    print @dynSQL

    FETCH cur into @dynSQL
END
CLOSE cur
DEALLOCATE cur
---------------------



   --HERE GOES YOUR TRUNCATES!!!!!
   --HERE GOES YOUR TRUNCATES!!!!!
   --HERE GOES YOUR TRUNCATES!!!!!

    truncate table your_table

   --HERE GOES YOUR TRUNCATES!!!!!
   --HERE GOES YOUR TRUNCATES!!!!!
   --HERE GOES YOUR TRUNCATES!!!!!

---------------------
--ADD CONSTRAINT:

DECLARE cur2 CURSOR FOR
SELECT
'
ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' + ForeignKeyConstraintTableName + ']
ADD CONSTRAINT ' + ForeignKeyConstraintName + ' FOREIGN KEY(' + ForeignKeyConstraintColumnName + ') REFERENCES [' + PrimaryKeyConstraintTableSchema + '].[' + PrimaryKeyConstraintTableName + '](' + PrimaryKeyConstraintColumnName + ')
'
FROM
@table

OPEN cur2

FETCH cur2 into @dynSQL
WHILE @@FETCH_STATUS = 0 
BEGIN
    exec(@dynSQL)

    print @dynSQL

    FETCH cur2 into @dynSQL
END
CLOSE cur2
DEALLOCATE cur2

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

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

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

下面是我写的一个脚本，以使这个过程自动化。我希望这能有所帮助。

SET NOCOUNT ON

-- GLOBAL VARIABLES
DECLARE @i int
DECLARE @Debug bit
DECLARE @Recycle bit
DECLARE @Verbose bit
DECLARE @TableName varchar(80)
DECLARE @ColumnName varchar(80)
DECLARE @ReferencedTableName varchar(80)
DECLARE @ReferencedColumnName varchar(80)
DECLARE @ConstraintName varchar(250)

DECLARE @CreateStatement varchar(max)
DECLARE @DropStatement varchar(max)   
DECLARE @TruncateStatement varchar(max)
DECLARE @CreateStatementTemp varchar(max)
DECLARE @DropStatementTemp varchar(max)
DECLARE @TruncateStatementTemp varchar(max)
DECLARE @Statement varchar(max)

        -- 1 = Will not execute statements 
 SET @Debug = 0
        -- 0 = Will not create or truncate storage table
        -- 1 = Will create or truncate storage table
 SET @Recycle = 0
        -- 1 = Will print a message on every step
 set @Verbose = 1

 SET @i = 1
    SET @CreateStatement = 'ALTER TABLE [dbo].[<tablename>]  WITH NOCHECK ADD  CONSTRAINT [<constraintname>] FOREIGN KEY([<column>]) REFERENCES [dbo].[<reftable>] ([<refcolumn>])'
    SET @DropStatement = 'ALTER TABLE [dbo].[<tablename>] DROP CONSTRAINT [<constraintname>]'
    SET @TruncateStatement = 'TRUNCATE TABLE [<tablename>]'

-- Drop Temporary tables
DROP TABLE #FKs

-- GET FKs
SELECT ROW_NUMBER() OVER (ORDER BY OBJECT_NAME(parent_object_id), clm1.name) as ID,
       OBJECT_NAME(constraint_object_id) as ConstraintName,
       OBJECT_NAME(parent_object_id) as TableName,
       clm1.name as ColumnName, 
       OBJECT_NAME(referenced_object_id) as ReferencedTableName,
       clm2.name as ReferencedColumnName
  INTO #FKs
  FROM sys.foreign_key_columns fk
       JOIN sys.columns clm1 
         ON fk.parent_column_id = clm1.column_id 
            AND fk.parent_object_id = clm1.object_id
       JOIN sys.columns clm2
         ON fk.referenced_column_id = clm2.column_id 
            AND fk.referenced_object_id= clm2.object_id
 WHERE OBJECT_NAME(parent_object_id) not in ('//tables that you do not wont to be truncated')
 ORDER BY OBJECT_NAME(parent_object_id)


-- Prepare Storage Table
IF Not EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Internal_FK_Definition_Storage')
   BEGIN
        IF @Verbose = 1
     PRINT '1. Creating Process Specific Tables...'

  -- CREATE STORAGE TABLE IF IT DOES NOT EXISTS
  CREATE TABLE [Internal_FK_Definition_Storage] 
  (
   ID int not null identity(1,1) primary key,
   FK_Name varchar(250) not null,
   FK_CreationStatement varchar(max) not null,
   FK_DestructionStatement varchar(max) not null,
   Table_TruncationStatement varchar(max) not null
  ) 
   END 
ELSE
   BEGIN
        IF @Recycle = 0
            BEGIN
                IF @Verbose = 1
       PRINT '1. Truncating Process Specific Tables...'

    -- TRUNCATE TABLE IF IT ALREADY EXISTS
    TRUNCATE TABLE [Internal_FK_Definition_Storage]    
      END
      ELSE
         PRINT '1. Process specific table will be recycled from previous execution...'
   END

IF @Recycle = 0
   BEGIN

  IF @Verbose = 1
     PRINT '2. Backing up Foreign Key Definitions...'

  -- Fetch and persist FKs             
  WHILE (@i <= (SELECT MAX(ID) FROM #FKs))
   BEGIN
    SET @ConstraintName = (SELECT ConstraintName FROM #FKs WHERE ID = @i)
    SET @TableName = (SELECT TableName FROM #FKs WHERE ID = @i)
    SET @ColumnName = (SELECT ColumnName FROM #FKs WHERE ID = @i)
    SET @ReferencedTableName = (SELECT ReferencedTableName FROM #FKs WHERE ID = @i)
    SET @ReferencedColumnName = (SELECT ReferencedColumnName FROM #FKs WHERE ID = @i)

    SET @DropStatementTemp = REPLACE(REPLACE(@DropStatement,'<tablename>',@TableName),'<constraintname>',@ConstraintName)
    SET @CreateStatementTemp = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@CreateStatement,'<tablename>',@TableName),'<column>',@ColumnName),'<constraintname>',@ConstraintName),'<reftable>',@ReferencedTableName),'<refcolumn>',@ReferencedColumnName)
    SET @TruncateStatementTemp = REPLACE(@TruncateStatement,'<tablename>',@TableName) 

    INSERT INTO [Internal_FK_Definition_Storage]
                        SELECT @ConstraintName, @CreateStatementTemp, @DropStatementTemp, @TruncateStatementTemp

    SET @i = @i + 1

    IF @Verbose = 1
       PRINT '  > Backing up [' + @ConstraintName + '] from [' + @TableName + ']'

   END
    END   
    ELSE 
       PRINT '2. Backup up was recycled from previous execution...'

       IF @Verbose = 1
     PRINT '3. Dropping Foreign Keys...'

    -- DROP FOREING KEYS
    SET @i = 1
    WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))
          BEGIN
             SET @ConstraintName = (SELECT FK_Name FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
    SET @Statement = (SELECT FK_DestructionStatement FROM [Internal_FK_Definition_Storage] WITH (NOLOCK) WHERE ID = @i)

    IF @Debug = 1 
       PRINT @Statement
    ELSE
       EXEC(@Statement)

    SET @i = @i + 1

    IF @Verbose = 1
       PRINT '  > Dropping [' + @ConstraintName + ']'
             END     

    IF @Verbose = 1
       PRINT '4. Truncating Tables...'

    -- TRUNCATE TABLES
    SET @i = 1
    WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))
          BEGIN
    SET @Statement = (SELECT Table_TruncationStatement FROM [Internal_FK_Definition_Storage] WHERE ID = @i)

    IF @Debug = 1 
       PRINT @Statement
    ELSE
       EXEC(@Statement)

    SET @i = @i + 1

    IF @Verbose = 1
       PRINT '  > ' + @Statement
          END

    IF @Verbose = 1
       PRINT '5. Re-creating Foreign Keys...'

    -- CREATE FOREING KEYS
    SET @i = 1
    WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))
          BEGIN
             SET @ConstraintName = (SELECT FK_Name FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
    SET @Statement = (SELECT FK_CreationStatement FROM [Internal_FK_Definition_Storage] WHERE ID = @i)

    IF @Debug = 1 
       PRINT @Statement
    ELSE
       EXEC(@Statement)

    SET @i = @i + 1

    IF @Verbose = 1
       PRINT '  > Re-creating [' + @ConstraintName + ']'
          END

    IF @Verbose = 1
       PRINT '6. Process Completed'

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

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

原因:

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