你如何截断使用TSQL数据库中所有表？(How do you truncate all tables

我有，我想在一个测试周期开始用新的数据重新加载数据库的测试环境。 我不感兴趣，重建整个数据库 - 只是简单的“重新设置”中的数据。

什么是去除使用TSQL的所有表中的所有数据的最佳方式？ 是否有系统存储过程，视图等可以使用？ 我不想手动创建和维护每个表 - 我宁愿它是动态截断表的语句。

Answer 1:

对于SQL 2005，

EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'

夫妇更多的链接为2000和2005/2008 ..

Answer 2:

这基本上是与任何适当的设计数据库的情况下 - - 当从有外键关系表中删除数据的时候，我们可以禁用所有的约束，删除所有数据，然后重新启用约束

-- 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（ '表名'，RESEED，newReseedValue）

当前标识值设置为newReseedValue。 如果没有行已经插入，因为它创建表，第一行执行DBCC CHECKIDENT将使用newReseedValue作为身份后插入。 否则，插入将使用newReseedValue + 1。如果newReseedValue的值小于在所述标识列的最大值的下一行，将在表中后续引用生成的错误消息2627。

感谢罗伯特·您指出的事实是禁用的约束不允许使用截形，约束必须被丢弃，然后重新创建

Answer 3:

这里的数据库擦脚本的王爹爹。 这将清除所有表和正确补种他们：

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;

享受，但要小心！

Answer 4:

这样做的最简单的方法是

1. 打开SQL Management Studio中
2. 导航到你的数据库
3. 右键单击并选择任务 - >生成脚本（图一）
4. 在“选择对象”屏幕中，选择“选择特定的对象”选项并勾选“表”（如图2）
5. 在下一屏幕上，选择“高级”，然后更改“脚本删除并创建”选项“脚本拖动和创建”（图三）
6. 选择脚本保存到一个新的编辑器窗口或文件，并根据需要运行。

这会给你删除并重新创建所有的表，而不需要担心调试或者是否你已经包含一切的脚本。 虽然这种执行不仅仅是一个截断更多，结果都是一样的。 请记住，你的自动递增的主键会从0开始，而不是将记住分配的最后一个值截断表。 您也可以从代码中执行这一点，如果你没有访问管理工作室您PreProd或生产环境。

1。

2。

3。

Answer 5:

截断所有的表，如果你没有你的表间的任何外键关系只会工作，因为SQL Server将不会允许您截断表的外键。

这另一种方法是确定与外键的表，并从这些先删除，那么你就可以截断表没有外键之后。

见http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65341和http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72957进一步的细节。

Answer 6:

我喜欢和MSSQL服务器Deveploper或企业使用替代的选择是创建空模式后，立即创建数据库的快照。 在这一点上，你可以只保留还原数据库恢复到快照。

Answer 7:

别这样！ 真的，不是一个好主意。

如果你知道你要截断该表，创建一个存储过程，而截断他们。 您可以修复，以避免外键的问题。

如果你真的想截断他们都（这样你就可以BCP加载它们例如），你会同样迅速地删除数据库和从头开始创建一个新的，这将对你确切地知道你在哪里了额外的好处。

Answer 8:

如果你想将数据保存在一个特定的表（即静态查找表），而删除/同样的分贝以内截断在其他表中的数据，那么你需要在它的异常循环。 这就是我一直在寻找，当我无意中发现了这个问题。

sp_MSForEachTable似乎马车给我（即不一致的行为与IF语句）这可能是为什么它通过MS无证。

declare @LastObjectID int = 0
declare @TableName nvarchar(100) = ''
set @LastObjectID = (select top 1 [object_id] from sys.tables where [object_id] > @LastObjectID order by [object_id])
while(@LastObjectID is not null)
begin
    set @TableName = (select top 1 [name] from sys.tables where [object_id] = @LastObjectID)

    if(@TableName not in ('Profiles', 'ClientDetails', 'Addresses', 'AgentDetails', 'ChainCodes', 'VendorDetails'))
    begin
        exec('truncate table [' + @TableName + ']')
    end 

    set @LastObjectID = (select top 1 [object_id] from sys.tables where [object_id] > @LastObjectID order by [object_id])
end

Answer 9:

创建一个空的“模板”数据库，采取了完全备份。 当你需要刷新，刚恢复使用WITH替换。 快速，简单，防弹。 如果这里的情侣对表或有需要一些基本数据（使您的应用程序的运行如配置信息，或只是基本信息），它处理这一点。

Answer 10:

截断所有表中最难的部分被删除并重新ading外键约束。

下面的查询创建的下降和创建与在@myTempTable每个表的名称每个约束语句。 如果你想生成这些所有的表，你可以简单的使用信息模式，而不是收集这些表名。

DECLARE @myTempTable TABLE (tableName varchar(200))
INSERT INTO @myTempTable(tableName) VALUES
('TABLE_ONE'),
('TABLE_TWO'),
('TABLE_THREE')


-- DROP FK Contraints
SELECT 'alter table '+quotename(schema_name(ob.schema_id))+
  '.'+quotename(object_name(ob.object_id))+ ' drop constraint ' + quotename(fk.name) 
  FROM sys.objects ob INNER JOIN sys.foreign_keys fk ON fk.parent_object_id = ob.object_id
  WHERE fk.referenced_object_id IN 
      (
         SELECT so.object_id 
         FROM sys.objects so JOIN sys.schemas sc
         ON so.schema_id = sc.schema_id
         WHERE so.name IN (SELECT * FROM @myTempTable)  AND sc.name=N'dbo'  AND type in (N'U'))


 -- CREATE FK Contraints
 SELECT 'ALTER TABLE [PIMSUser].[dbo].[' +cast(c.name as varchar(255)) + '] WITH NOCHECK ADD CONSTRAINT ['+ cast(f.name as varchar(255)) +'] FOREIGN KEY (['+ cast(fc.name as varchar(255)) +'])
      REFERENCES [PIMSUser].[dbo].['+ cast(p.name as varchar(255)) +'] (['+cast(rc.name as varchar(255))+'])'
FROM  sysobjects f
      INNER JOIN sys.sysobjects c ON f.parent_obj = c.id
      INNER JOIN sys.sysreferences r ON f.id = r.constid
      INNER JOIN sys.sysobjects p ON r.rkeyid = p.id
      INNER JOIN sys.syscolumns rc ON r.rkeyid = rc.id and r.rkey1 = rc.colid
      INNER JOIN sys.syscolumns fc ON r.fkeyid = fc.id and r.fkey1 = fc.colid
WHERE 
      f.type = 'F'
      AND
      cast(p.name as varchar(255)) IN (SELECT * FROM @myTempTable)

我然后只复制出来运行报表 - 但有一点开发的努力，你可以使用光标来动态地运行它们。

Answer 11:

这是很容易（甚至可能更快）脚本你的数据库，然后就下降，从脚本创建它。

Answer 12:

这是做这件事 ......也有可能10人是更好/更高效，但它听起来像是这是极少做，所以这里去...

得到的名单tables由sysobjects ，然后在那些游标循环，调用sp_execsql('truncate table ' + @table_name)每个iteration 。

Answer 13:

我不明白为什么清除数据会比一个脚本更好地删除并重新创建每个表。

这或保持备份您的空DB的并可以通过旧恢复

Answer 14:

截断表之前你必须删除所有外键。 使用该脚本来生成最终的脚本删除并重新创建数据库中所有外键。 请设置@action变量来“创造”或“DROP”。

Answer 15:

运行注释掉部分一次，填充_TruncateList表与要截断的表，然后运行该脚本的其余部分。 该_ScriptLog表将需要一段时间来进行清理，如果你这样做了很多。

如果你想要做的所有表可以修改这一点，只是把SELECT INTO名从#TruncateList 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

Answer 16:

这是一个有点晚，但它可能会帮助别人。 我创建了一个程序有时回其执行以下操作使用T-SQL：

1. 存储所有约束在一个临时表
2. 删除所有限制
3. 截断所有表与一些表的例外，它不需要截断
4. 重新创建所有约束。

我列出了它在我的博客在这里

Answer 17:

选择从INFORMATION_SCHEMA.TABLES + TABLE_NAME“从删除”，其中TABLE_TYPE =“基本表”

其中，结果来了。

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

文章来源: How do you truncate all tables in a database using TSQL?