运行一次注释掉的部分，用希望截断的表填充_TruncateList表，然后运行脚本的其余部分。如果您经常这样做，_ScriptLog表将需要随着时间的推移被清理。

如果你想做所有的表，你可以修改这个，只要把SELECT name放入#TruncateList FROM sys.tables。然而，你通常不想做所有的事情。

此外，这将影响数据库中的所有外键，如果它对您的应用程序太生硬，您也可以修改它。这不是我的目的。

/*
CREATE TABLE _ScriptLog 
(
    ID Int NOT NULL Identity(1,1)
    , DateAdded DateTime2 NOT NULL DEFAULT GetDate()
    , Script NVarChar(4000) NOT NULL
)

CREATE UNIQUE CLUSTERED INDEX IX_ScriptLog_DateAdded_ID_U_C ON _ScriptLog
(
    DateAdded
    , ID
)

CREATE TABLE _TruncateList
(
    TableName SysName PRIMARY KEY
)
*/
IF OBJECT_ID('TempDB..#DropFK') IS NOT NULL BEGIN
    DROP TABLE #DropFK
END

IF OBJECT_ID('TempDB..#TruncateList') IS NOT NULL BEGIN
    DROP TABLE #TruncateList
END

IF OBJECT_ID('TempDB..#CreateFK') IS NOT NULL BEGIN
    DROP TABLE #CreateFK
END

SELECT Scripts = 'ALTER TABLE ' + '[' + OBJECT_NAME(f.parent_object_id)+ ']'+
' DROP  CONSTRAINT ' + '[' + f.name  + ']'
INTO #DropFK
FROM .sys.foreign_keys AS f
INNER JOIN .sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id

SELECT TableName
INTO #TruncateList
FROM _TruncateList

SELECT Scripts = 'ALTER TABLE ' + const.parent_obj + '
    ADD CONSTRAINT ' + const.const_name + ' FOREIGN KEY (
            ' + const.parent_col_csv + '
            ) REFERENCES ' + const.ref_obj + '(' + const.ref_col_csv + ')
'
INTO #CreateFK
FROM (
    SELECT QUOTENAME(fk.NAME) AS [const_name]
        ,QUOTENAME(schParent.NAME) + '.' + QUOTENAME(OBJECT_name(fkc.parent_object_id)) AS [parent_obj]
        ,STUFF((
                SELECT ',' + QUOTENAME(COL_NAME(fcP.parent_object_id, fcp.parent_column_id))
                FROM sys.foreign_key_columns AS fcP
                WHERE fcp.constraint_object_id = fk.object_id
                FOR XML path('')
                ), 1, 1, '') AS [parent_col_csv]
        ,QUOTENAME(schRef.NAME) + '.' + QUOTENAME(OBJECT_NAME(fkc.referenced_object_id)) AS [ref_obj]
        ,STUFF((
                SELECT ',' + QUOTENAME(COL_NAME(fcR.referenced_object_id, fcR.referenced_column_id))
                FROM sys.foreign_key_columns AS fcR
                WHERE fcR.constraint_object_id = fk.object_id
                FOR XML path('')
                ), 1, 1, '') AS [ref_col_csv]
    FROM sys.foreign_key_columns AS fkc
    INNER JOIN sys.foreign_keys AS fk ON fk.object_id = fkc.constraint_object_id
    INNER JOIN sys.objects AS oParent ON oParent.object_id = fkc.parent_object_id
    INNER JOIN sys.schemas AS schParent ON schParent.schema_id = oParent.schema_id
    INNER JOIN sys.objects AS oRef ON oRef.object_id = fkc.referenced_object_id
    INNER JOIN sys.schemas AS schRef ON schRef.schema_id = oRef.schema_id
    GROUP BY fkc.parent_object_id
        ,fkc.referenced_object_id
        ,fk.NAME
        ,fk.object_id
        ,schParent.NAME
        ,schRef.NAME
    ) AS const
ORDER BY const.const_name

INSERT INTO _ScriptLog (Script)
SELECT Scripts
FROM #CreateFK

DECLARE @Cmd NVarChar(4000)
    , @TableName SysName

WHILE 0 < (SELECT Count(1) FROM #DropFK) BEGIN
    SELECT TOP 1 @Cmd = Scripts 
    FROM #DropFK

    EXEC (@Cmd)

    DELETE #DropFK WHERE Scripts = @Cmd
END

WHILE 0 < (SELECT Count(1) FROM #TruncateList) BEGIN
    SELECT TOP 1 @Cmd = N'TRUNCATE TABLE ' +  TableName
        , @TableName = TableName
    FROM #TruncateList

    EXEC (@Cmd)

    DELETE #TruncateList WHERE TableName = @TableName
END

WHILE 0 < (SELECT Count(1) FROM #CreateFK) BEGIN
    SELECT TOP 1 @Cmd = Scripts 
    FROM #CreateFK

    EXEC (@Cmd)

    DELETE #CreateFK WHERE Scripts = @Cmd
END

这是数据库擦除脚本之王。它将清除所有表并正确地重新播种:

SET QUOTED_IDENTIFIER ON;
EXEC sp_MSforeachtable 'SET QUOTED_IDENTIFIER ON; ALTER TABLE ? NOCHECK CONSTRAINT ALL'  
EXEC sp_MSforeachtable 'SET QUOTED_IDENTIFIER ON; ALTER TABLE ? DISABLE TRIGGER ALL'  
EXEC sp_MSforeachtable 'SET QUOTED_IDENTIFIER ON; DELETE FROM ?'  
EXEC sp_MSforeachtable 'SET QUOTED_IDENTIFIER ON; ALTER TABLE ? CHECK CONSTRAINT ALL'  
EXEC sp_MSforeachtable 'SET QUOTED_IDENTIFIER ON; ALTER TABLE ? ENABLE TRIGGER ALL' 
EXEC sp_MSforeachtable 'SET QUOTED_IDENTIFIER ON';

IF NOT EXISTS (
    SELECT
        *
    FROM
        SYS.IDENTITY_COLUMNS
        JOIN SYS.TABLES ON SYS.IDENTITY_COLUMNS.Object_ID = SYS.TABLES.Object_ID
    WHERE
        SYS.TABLES.Object_ID = OBJECT_ID('?') AND SYS.IDENTITY_COLUMNS.Last_Value IS NULL
)
AND OBJECTPROPERTY( OBJECT_ID('?'), 'TableHasIdentity' ) = 1

    DBCC CHECKIDENT ('?', RESEED, 0) WITH NO_INFOMSGS;

享受吧，但要小心!

当处理从具有外键关系的表中删除数据时(这基本上是任何设计良好的数据库的情况)，我们可以禁用所有约束，删除所有数据，然后重新启用约束

-- disable all 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"

这里有更多关于禁用约束和触发器的信息

如果某些表有标识列，我们可能需要重新播种它们

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

请注意，RESEED的行为在全新的表和之前从BOL中插入了一些数据的表之间有所不同:

DBCC CHECKIDENT ('table_name', RESEED, newReseedValue) 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.

感谢Robert指出禁用约束不允许使用截断的事实，约束必须被删除，然后重新创建

select 'delete from ' +TABLE_NAME from INFORMATION_SCHEMATABLE_TYPE='BASE TABLE'的表

结果就在那里。

在查询窗口复制粘贴并运行命令

虽然有点晚了，但也许能帮到别人。 我有时会创建一个过程，使用T-SQL执行以下操作:

将所有约束存储在临时表中 删除所有约束 除某些不需要截断的表外，截断所有表 重新创建所有约束。

我已经把它列在我的博客上了

只有当你的表之间没有任何外键关系时，才能截断所有的表，因为SQL Server不允许你用外键截断一个表。

另一种方法是先确定有外键的表并删除它们，然后再截断没有外键的表。

详见http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65341和http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72957。