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

使用TSQL从所有表中删除所有数据的最佳方法是什么?是否有可以使用的系统存储过程、视图等?我不想为每个表手动创建和维护截断表语句-我更希望它是动态的。


当前回答

如果你想在一个特定的表(即静态查找表)中保留数据,同时删除/截断同一数据库中其他表中的数据,那么你需要一个循环,其中包含异常。这就是我在无意中发现这个问题时所寻找的。

sp_MSForEachTable对我来说似乎有bug(即与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

其他回答

制作一个空的“模板”数据库,进行完全备份。当您需要刷新时,只需使用WITH REPLACE恢复。快速,简单,防弹。如果这里或那里的几个表需要一些基本数据(例如。配置信息,或让你的应用程序运行的基本信息),它也会处理这些。

用脚本删除数据库要容易得多(甚至可能更快),然后直接从脚本中删除并创建数据库。

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

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;

享受吧,但要小心!

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

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

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

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

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

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