@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;

删除然后重置自动增量:

delete from tablename;

then

ALTER TABLE tablename AUTO_INCREMENT = 1;

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

delete from tablename

DBCC CHECKIDENT ('tablename', RESEED, 0)

编辑:修正的SQL Server语法

这是我对这个问题的解决方案。我用它来改变PK，但想法是一样的。希望这将是有用的)

PRINT 'Script starts'

DECLARE @foreign_key_name varchar(255)
DECLARE @keycnt int
DECLARE @foreign_table varchar(255)
DECLARE @foreign_column_1 varchar(255)
DECLARE @foreign_column_2 varchar(255)
DECLARE @primary_table varchar(255)
DECLARE @primary_column_1 varchar(255)
DECLARE @primary_column_2 varchar(255)
DECLARE @TablN varchar(255)

-->> Type the primary table name
SET @TablN = ''
---------------------------------------------------------------------------------------    ------------------------------
--Here will be created the temporary table with all reference FKs
---------------------------------------------------------------------------------------------------------------------
PRINT 'Creating the temporary table'
select cast(f.name  as varchar(255)) as foreign_key_name
    , r.keycnt
    , cast(c.name as  varchar(255)) as foreign_table
    , cast(fc.name as varchar(255)) as  foreign_column_1
    , cast(fc2.name as varchar(255)) as foreign_column_2
    , cast(p.name as varchar(255)) as primary_table
    , cast(rc.name as varchar(255))  as primary_column_1
    , cast(rc2.name as varchar(255)) as  primary_column_2
    into #ConTab
    from sysobjects f
    inner join sysobjects c on  f.parent_obj = c.id 
    inner join sysreferences r on f.id =  r.constid
    inner join sysobjects p on r.rkeyid = p.id
    inner  join syscolumns rc on r.rkeyid = rc.id and r.rkey1 = rc.colid
    inner  join syscolumns fc on r.fkeyid = fc.id and r.fkey1 = fc.colid
    left join  syscolumns rc2 on r.rkeyid = rc2.id and r.rkey2 = rc.colid
    left join  syscolumns fc2 on r.fkeyid = fc2.id and r.fkey2 = fc.colid
    where f.type =  'F' and p.name = @TablN
 ORDER BY cast(p.name as varchar(255))
---------------------------------------------------------------------------------------------------------------------
--Cursor, below, will drop all reference FKs
---------------------------------------------------------------------------------------------------------------------
DECLARE @CURSOR CURSOR
/*Fill in cursor*/

PRINT 'Cursor 1 starting. All refernce FK will be droped'

SET @CURSOR  = CURSOR SCROLL
FOR
select foreign_key_name
    , keycnt
    , foreign_table
    , foreign_column_1
    , foreign_column_2
    , primary_table
    , primary_column_1
    , primary_column_2
    from #ConTab

OPEN @CURSOR

FETCH NEXT FROM @CURSOR INTO @foreign_key_name, @keycnt, @foreign_table,         @foreign_column_1, @foreign_column_2, 
                        @primary_table, @primary_column_1, @primary_column_2

WHILE @@FETCH_STATUS = 0
BEGIN

    EXEC ('ALTER TABLE ['+@foreign_table+'] DROP CONSTRAINT ['+@foreign_key_name+']')

FETCH NEXT FROM @CURSOR INTO @foreign_key_name, @keycnt, @foreign_table, @foreign_column_1, @foreign_column_2, 
                         @primary_table, @primary_column_1, @primary_column_2
END
CLOSE @CURSOR
PRINT 'Cursor 1 finished work'
---------------------------------------------------------------------------------------------------------------------
--Here you should provide the chainging script for the primary table
---------------------------------------------------------------------------------------------------------------------

PRINT 'Altering primary table begin'

TRUNCATE TABLE table_name

PRINT 'Altering finished'

---------------------------------------------------------------------------------------------------------------------
--Cursor, below, will add again all reference FKs
--------------------------------------------------------------------------------------------------------------------

PRINT 'Cursor 2 starting. All refernce FK will added'
SET @CURSOR  = CURSOR SCROLL
FOR
select foreign_key_name
    , keycnt
    , foreign_table
    , foreign_column_1
    , foreign_column_2
    , primary_table
    , primary_column_1
    , primary_column_2
    from #ConTab

OPEN @CURSOR

FETCH NEXT FROM @CURSOR INTO @foreign_key_name, @keycnt, @foreign_table, @foreign_column_1, @foreign_column_2, 
                         @primary_table, @primary_column_1, @primary_column_2

WHILE @@FETCH_STATUS = 0
BEGIN

    EXEC ('ALTER TABLE [' +@foreign_table+ '] WITH NOCHECK ADD  CONSTRAINT [' +@foreign_key_name+ '] FOREIGN KEY(['+@foreign_column_1+'])
        REFERENCES [' +@primary_table+'] (['+@primary_column_1+'])')

    EXEC ('ALTER TABLE [' +@foreign_table+ '] CHECK CONSTRAINT [' +@foreign_key_name+']')

FETCH NEXT FROM @CURSOR INTO @foreign_key_name, @keycnt, @foreign_table, @foreign_column_1, @foreign_column_2, 
                         @primary_table, @primary_column_1, @primary_column_2
END
CLOSE @CURSOR
PRINT 'Cursor 2 finished work'
---------------------------------------------------------------------------------------------------------------------
PRINT 'Temporary table droping'
drop table #ConTab
PRINT 'Finish'

我写了以下方法，并尝试将它们参数化，这样你就可以在查询文档中运行它们，或者轻松地使用它们制作一个有用的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 -在phpmyadmin中输入 2 -点击左列的表名 3 -点击操作(顶部菜单) 4 -点击“清空表格(TRUNCATE)” 5 -禁用框“启用外键检查” 6 -完成!

图片教程链接 教程:http://www.imageno.com/wz6gv1wuqajrpic.html (对不起，我没有足够的声誉上传图片:P)