SQL TRUNCATE DATABASE?如何截断所有表

| 我使用SQL Server 2008 R2。 是否有SQL命令清空数据库,而不必截断我所有的20个表? 我只想删除数据而不是结构。     
已邀请:
您可以使用sp_MSforeachtable存储过程,如下所示:
USE MyDatabase
EXEC sp_MSforeachtable \'TRUNCATE TABLE ?\'
请注意,这将删除(通过截断)所有用户表中的所有数据。如果由于外键等原因导致无法截断,则可以像删除一样运行:
USE MyDatabase
EXEC sp_MSforeachtable \'DELETE FROM ?\'
    
我用这个脚本
EXEC sp_MSForEachTable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’
EXEC sp_MSForEachTable ‘DELETE FROM ?’
EXEC sp_MSForEachTable ‘ALTER TABLE ? CHECK CONSTRAINT ALL’
GO
    
当您具有外键关系时,可接受的答案不太有效。在这种情况下,您必须删除约束并重新创建它们。以下是基于此处答案的存储过程
CREATE PROCEDURE [dbo].[deleteAllDataFromAllTables] AS
SET NOCOUNT ON

    DECLARE @dropAndCreateConstraintsTable TABLE ( DropStmt varchar(max) , CreateStmt varchar(max) )

    insert @dropAndCreateConstraintsTable select
      DropStmt = \'ALTER TABLE [\' + ForeignKeys.ForeignTableSchema + 
          \'].[\' + ForeignKeys.ForeignTableName + 
          \'] DROP CONSTRAINT [\' + ForeignKeys.ForeignKeyName + \']; \'
    ,  CreateStmt = \'ALTER TABLE [\' + ForeignKeys.ForeignTableSchema + 
          \'].[\' + ForeignKeys.ForeignTableName + 
          \'] WITH CHECK ADD CONSTRAINT [\' +  ForeignKeys.ForeignKeyName + 
          \'] FOREIGN KEY([\' + ForeignKeys.ForeignTableColumn + 
          \']) REFERENCES [\' + schema_name(sys.objects.schema_id) + \'].[\' +
      sys.objects.[name] + \']([\' +
      sys.columns.[name] + \']); \'
     from sys.objects
      inner join sys.columns
        on (sys.columns.[object_id] = sys.objects.[object_id])
      inner join (
        select sys.foreign_keys.[name] as ForeignKeyName
         ,schema_name(sys.objects.schema_id) as ForeignTableSchema
         ,sys.objects.[name] as ForeignTableName
         ,sys.columns.[name]  as ForeignTableColumn
         ,sys.foreign_keys.referenced_object_id as referenced_object_id
         ,sys.foreign_key_columns.referenced_column_id as referenced_column_id
         from sys.foreign_keys
          inner join sys.foreign_key_columns
            on (sys.foreign_key_columns.constraint_object_id
              = sys.foreign_keys.[object_id])
          inner join sys.objects
            on (sys.objects.[object_id]
              = sys.foreign_keys.parent_object_id)
            inner join sys.columns
              on (sys.columns.[object_id]
                = sys.objects.[object_id])
               and (sys.columns.column_id
                = sys.foreign_key_columns.parent_column_id)
        ) ForeignKeys
        on (ForeignKeys.referenced_object_id = sys.objects.[object_id])
         and (ForeignKeys.referenced_column_id = sys.columns.column_id)
     where (sys.objects.[type] = \'U\')
      and (sys.objects.[name] not in (\'sysdiagrams\'))


    DECLARE @DropStatement nvarchar(max)
    DECLARE @RecreateStatement nvarchar(max)

    DECLARE C1 CURSOR READ_ONLY
    FOR
    SELECT DropStmt from @dropAndCreateConstraintsTable
    OPEN C1

    FETCH NEXT FROM C1 INTO @DropStatement

    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT \'Executing \' + @DropStatement
        execute sp_executesql @DropStatement
        FETCH NEXT FROM C1 INTO @DropStatement
    END
    CLOSE C1
    DEALLOCATE C1

    DECLARE @DeleteTableStatement nvarchar(max)
    DECLARE C2 CURSOR READ_ONLY
    FOR
    SELECT \'Delete From [dbo].[\' + TABLE_NAME + \']\' from INFORMATION_SCHEMA.TABLES 
        WHERE TABLE_SCHEMA = \'dbo\' -- Change your schema appropriately.
    OPEN C2

    FETCH NEXT FROM C2 INTO @DeleteTableStatement

    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT \'Executing \' + @DeleteTableStatement
        execute sp_executesql @DeleteTableStatement
        FETCH NEXT FROM C2 INTO  @DeleteTableStatement
    END
    CLOSE C2
    DEALLOCATE C2

    DECLARE C3 CURSOR READ_ONLY
    FOR
    SELECT CreateStmt from @dropAndCreateConstraintsTable
    OPEN C3

    FETCH NEXT FROM C3 INTO @RecreateStatement

    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT \'Executing \' + @RecreateStatement
        execute sp_executesql @RecreateStatement
        FETCH NEXT FROM C3 INTO  @RecreateStatement
    END
    CLOSE C3
    DEALLOCATE C3

GO
    
提供的大多数解决方案要么不使用与DELETE不同的TRUNCATE,要么不处理外键约束问题。 Chaitanya提供的解决方案很接近,但是它只能使用DELETE,并且可以在存储过程中完成,这似乎不适合在数据库中处理所有数据的情况。 所以,下面是我的变体,它使用TRUNCATE并解决了外键约束问题。
    /* TRUNCATE ALL TABLES IN A DATABASE */

DECLARE @dropAndCreateConstraintsTable TABLE
        (
         DropStmt VARCHAR(MAX)
        ,CreateStmt VARCHAR(MAX)
        )
/* Gather information to drop and then recreate the current foreign key constraints  */
INSERT  @dropAndCreateConstraintsTable
        SELECT  DropStmt = \'ALTER TABLE [\' + ForeignKeys.ForeignTableSchema
                + \'].[\' + ForeignKeys.ForeignTableName + \'] DROP CONSTRAINT [\'
                + ForeignKeys.ForeignKeyName + \']; \'
               ,CreateStmt = \'ALTER TABLE [\' + ForeignKeys.ForeignTableSchema
                + \'].[\' + ForeignKeys.ForeignTableName
                + \'] WITH CHECK ADD CONSTRAINT [\' + ForeignKeys.ForeignKeyName
                + \'] FOREIGN KEY([\' + ForeignKeys.ForeignTableColumn
                + \']) REFERENCES [\' + SCHEMA_NAME(sys.objects.schema_id)
                + \'].[\' + sys.objects.[name] + \']([\' + sys.columns.[name]
                + \']); \'
        FROM    sys.objects
        INNER JOIN sys.columns
                ON ( sys.columns.[object_id] = sys.objects.[object_id] )
        INNER JOIN ( SELECT sys.foreign_keys.[name] AS ForeignKeyName
                           ,SCHEMA_NAME(sys.objects.schema_id) AS ForeignTableSchema
                           ,sys.objects.[name] AS ForeignTableName
                           ,sys.columns.[name] AS ForeignTableColumn
                           ,sys.foreign_keys.referenced_object_id AS referenced_object_id
                           ,sys.foreign_key_columns.referenced_column_id AS referenced_column_id
                     FROM   sys.foreign_keys
                     INNER JOIN sys.foreign_key_columns
                            ON ( sys.foreign_key_columns.constraint_object_id = sys.foreign_keys.[object_id] )
                     INNER JOIN sys.objects
                            ON ( sys.objects.[object_id] = sys.foreign_keys.parent_object_id )
                     INNER JOIN sys.columns
                            ON ( sys.columns.[object_id] = sys.objects.[object_id] )
                               AND ( sys.columns.column_id = sys.foreign_key_columns.parent_column_id )
                   ) ForeignKeys
                ON ( ForeignKeys.referenced_object_id = sys.objects.[object_id] )
                   AND ( ForeignKeys.referenced_column_id = sys.columns.column_id )
        WHERE   ( sys.objects.[type] = \'U\' )
                AND ( sys.objects.[name] NOT IN ( \'sysdiagrams\' ) )

/* SELECT * FROM @dropAndCreateConstraintsTable AS DACCT */

DECLARE @DropStatement NVARCHAR(MAX)
DECLARE @RecreateStatement NVARCHAR(MAX)

/* Drop Constraints */
DECLARE C1 CURSOR READ_ONLY
FOR
        SELECT  DropStmt
        FROM    @dropAndCreateConstraintsTable
OPEN C1

FETCH NEXT FROM C1 INTO @DropStatement

WHILE @@FETCH_STATUS = 0
      BEGIN
            PRINT \'Executing \' + @DropStatement
            EXECUTE sp_executesql @DropStatement
            FETCH NEXT FROM C1 INTO @DropStatement
      END
CLOSE C1
DEALLOCATE C1

/* Truncate all tables in the database in the dbo schema */
DECLARE @DeleteTableStatement NVARCHAR(MAX)
DECLARE C2 CURSOR READ_ONLY
FOR
        SELECT  \'TRUNCATE TABLE [dbo].[\' + TABLE_NAME + \']\'
        FROM    INFORMATION_SCHEMA.TABLES
        WHERE   TABLE_SCHEMA = \'dbo\'
                AND TABLE_TYPE = \'BASE TABLE\'
  /* Change your schema appropriately if you don\'t want to use dbo */
OPEN C2

FETCH NEXT FROM C2 INTO @DeleteTableStatement

WHILE @@FETCH_STATUS = 0
      BEGIN
            PRINT \'Executing \' + @DeleteTableStatement
            EXECUTE sp_executesql @DeleteTableStatement
            FETCH NEXT FROM C2 INTO @DeleteTableStatement
      END
CLOSE C2
DEALLOCATE C2

/* Recreate foreign key constraints  */
DECLARE C3 CURSOR READ_ONLY
FOR
        SELECT  CreateStmt
        FROM    @dropAndCreateConstraintsTable
OPEN C3

FETCH NEXT FROM C3 INTO @RecreateStatement

WHILE @@FETCH_STATUS = 0
      BEGIN
            PRINT \'Executing \' + @RecreateStatement
            EXECUTE sp_executesql @RecreateStatement
            FETCH NEXT FROM C3 INTO @RecreateStatement
      END
CLOSE C3
DEALLOCATE C3

GO
    
执行这个
EXEC sp_MSforeachtable \'PRINT \'\'ALTER TABLE ? NOCHECK CONSTRAINT ALL\'\'\'
EXEC sp_MSforeachtable \'print \'\'DELETE FROM ?\'\'\'
EXEC sp_MSforeachtable \'print \'\'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all\'\'\'
复制打印结果后,将其粘贴到“查询”字段并执行。它将截断所有表。     
DECLARE @nombre NVARCHAR(100)
DECLARE @tablas TABLE(nombre nvarchar(100))

INSERT INTO @tablas
SELECT t.TABLE_SCHEMA+ \'.\'+t.TABLE_NAME FROM INFORMATION_SCHEMA.TABLES T
DECLARE @contador INT=0

SELECT @contador=COUNT(*) FROM INFORMATION_SCHEMA.TABLES

WHILE @contador>0 
BEGIN
    SELECT TOP 1 @nombre=nombre FROM @tablas 
    DECLARE @sql NVARCHAR(500)=\'\'
    SET @sql =@sql+\'Truncate table  \'+@nombre
    EXEC (@sql)
    SELECT @sql
    SET @contador=@contador-1   
    DELETE TOP (1) @tablas 
END
    
从Boycs答案和mtmurdock的后续答案中得出一点,我在所有开发或暂存数据库上都存储了以下proc。如果需要添加in语句来重新设置某些列的数据,我已经添加了一些开关以满足我自己的要求。 (注意:我会将其添加为Boycs出色答案的注释,但我还没有足够的声誉来做到这一点。因此,请接受我的歉意,将其添加为全新答案。)
ALTER PROCEDURE up_ResetEntireDatabase
@IncludeIdentReseed BIT,
@IncludeDataReseed BIT
AS

EXEC sp_MSForEachTable \'ALTER TABLE ? NOCHECK CONSTRAINT ALL\'
EXEC sp_MSForEachTable \'DELETE FROM ?\'

IF @IncludeIdentReseed = 1
BEGIN
    EXEC sp_MSForEachTable \'DBCC CHECKIDENT (\'\'?\'\' , RESEED, 1)\'
END

EXEC sp_MSForEachTable \'ALTER TABLE ? CHECK CONSTRAINT ALL\'

IF @IncludeDataReseed = 1
BEGIN
    -- Populate Core Data Table Here
END
GO
然后,一旦准备好,执行就非常简单:
EXEC up_ResetEntireDatabase 1, 1
    
没有废话脚本:
EXEC sp_MSForEachTable \'ALTER TABLE ? NOCHECK CONSTRAINT ALL\'
EXEC sp_MSForEachTable \'DELETE FROM ?\'
EXEC sp_MSForEachTable \'ALTER TABLE ? CHECK CONSTRAINT ALL\'
EXEC sp_MSforeachtable \'DBCC CHECKIDENT ( \'\'?\'\', RESEED, 0)\'
GO
如果表中有外键,则截断仍然不起作用。 此脚本还将重置所有标识列。     
如果只想截断特定模式中的所有表,也可以使用此存储过程:
-- =============================================
-- Author:      Ben de Ridder
-- Create date: 20160513
-- Description: Truncate all tables in schema
-- =============================================
CREATE PROCEDURE TruncateAllTablesInSchema
    @schema nvarchar(50)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @table nVARCHAR(255)

    DECLARE db_cursor CURSOR FOR 
        select t.name
          from sys.tables t inner join
               sys.schemas s on 
                    t.schema_id=s.schema_id
         where s.name=@schema
         order by 1

    OPEN db_cursor   

    FETCH NEXT FROM db_cursor INTO @table   

    WHILE @@FETCH_STATUS = 0   
    BEGIN   
           declare @sql nvarchar(1000)

           set @sql = \'truncate table [\' + @schema + \'].[\' + @table + \']\'

           exec sp_sqlexec @sql

           FETCH NEXT FROM db_cursor INTO @table   
    END   

    CLOSE db_cursor   
    DEALLOCATE db_cursor 

END
GO
    
---- Remove Constraint ----
EXEC sp_MSForEachTable \"ALTER TABLE ? NOCHECK CONSTRAINT all\"

---- Delete Data ----
EXEC sp_MSForEachTable \"DELETE FROM ?\"

---- Add Constraint ----
EXEC sp_MSForEachTable \"ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all\"

---- Reset Identity value ----
EXEC sp_MSForEachTable \"DBCC CHECKIDENT ( \'?\', RESEED, 0)\"
    

要回复问题请先登录注册