您好,UncleToo欢迎您!  为了更好的浏览本站,请使用高版本浏览器
RSS  Tag     设为首页 | 加入收藏
 您所在的位置:首页 > 数据库技术 > SQL Server

如何删除SQL Server数据库中所有表的数据

作者:UncleToo翻译  来源:mssqltips  日期:2014-05-13 8:26:27
收藏  评论:( 0 )  阅读:393

       不管我们在DBMS开发过程中,还是平时的数据库维护工作中,都会遇到这样的问题:在现有的SQL Server数据库中加载一个低版本的数据库数据,或者更改一张数据表,如果数据库中存在外键约束,那么我们就无法完成了,需要先清除约束及数据才能继续。然而这回事一件很乏味很费时的工作,因此我发现有一个很好的办法能解决这个问题,利用TRUNCATE来实现,下面是具体的方法:

首先我们建立测试数据库及数据

Create DATABASE ForeignKeyTest
GO
USE ForeignKeyTest
GO
Create TABLE T1
       (
        T1Id INT IDENTITY
                 PRIMARY KEY
                 NOT NULL
       ,Customer VARCHAR(50) NOT NULL
       )
GO
Create TABLE T2
       (
        T2Id INT IDENTITY
                 PRIMARY KEY
                 NOT NULL
       ,T1Id INT NOT NULL
       ,OrderNum INT NOT NULL
       )
GO
Create TABLE T3
       (
        T3Id INT IDENTITY
                 PRIMARY KEY
                 NOT NULL
       ,T2Id INT NOT NULL
       ,ItemId INT NOT NULL
       ,Qty INT NOT NULL
       )
GO
Create TABLE Item
       (
        ItemId INT IDENTITY
                   PRIMARY KEY
                   NOT NULL
       ,Item VARCHAR(50)
       )
GO
Alter TABLE dbo.T2
ADD CONSTRAINT FK_T2_T1 FOREIGN KEY( T1Id) REFERENCES dbo.T1 ( T1Id)
GO
Alter TABLE dbo.T3
ADD CONSTRAINT FK_T3_T2 FOREIGN KEY( T2Id) REFERENCES dbo.T2 (T2Id)
GO
Alter TABLE dbo.T3
ADD CONSTRAINT FK_T3_Item FOREIGN KEY(ItemId) REFERENCES dbo.Item(ItemId)
GO
Insert dbo.T1
        ( Customer )
Select 'FirstCust'
UNION
Select 'SecondCust'
UNION
Select 'ThirdCust' ;
Insert dbo.T2
        ( T1Id, orderNum )
Select 1, 1
UNION
Select 1, 2
UNION
Select 2, 3
UNION
Select 3, 4
UNION
Select 3, 5 ;
Insert dbo.Item
        ( Item )
Select 'Gunk'
UNION
Select 'Slop'
UNION
Select 'Glop'
UNION
Select 'Crud' ;
Insert dbo.T3
        ( T2Id, ItemId, Qty )
Select 1,3,5
UNION
Select 1,2,2
UNION
Select 2,1,4
UNION
Select 3,3,10;

数据库创建完成后就存在这样的关联关系:

表中数据如下:


此时,如果你TRUNCATE任何一张表,如:

BEGIN TRAN
TRUNCATE TABLE dbo.T2
ROLLBACK

都会有如下的错误提示:


如果使用Delete删除任何一张表的数据,如:

BEGIN TRAN
Delete dbo.T1
ROLLBACK

也会出现如下错误提示:


如果我们仅仅使用Delete删除数据,那么可以先将约束条件删除,然后在执行Delete,如:

BEGIN TRAN
Alter TABLE dbo.T1 NOCHECK CONSTRAINT ALL
Alter TABLE dbo.T2
NOCHECK CONSTRAINT ALL
Delete dbo.T1
Select * FROM dbo.T1 AS T
Alter TABLE dbo.T1 CHECK CONSTRAINT ALL
Alter TABLE dbo.T2 CHECK CONSTRAINT ALL
ROLLBACK

以上代码可以成功执行,并且T1表的数据将被清空


但是如果我们想利用TRUNCATE删除数据,采用上的方法是不可行的,如:

BEGIN TRAN
Alter TABLE dbo.T1 NOCHECK CONSTRAINT ALL
Alter TABLE dbo.T2 NOCHECK CONSTRAINT ALL
TRUNCATE TABLE  dbo.T1
Select * FROM dbo.T1 AS T
ROLLBACK

执行后将会提示如下错误:


为了解决问题,我们需要使用TRUNCATE,因此,我们需要删除所有的外键约束,然后删除数据,最后再将约束恢复。下面是实现的步骤:

1、创建一个表变量来存储约束

2、利用游标删除所有约束

3、TRUNCATE所有表

4、重新创建所有的约束


完整的SQL脚本:

/* 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  --Test statement*/
DECLARE @DropStatement NVARCHAR(MAX)
DECLARE @RecreateStatement NVARCHAR(MAX)
/* Drop Constraints */
DECLARE Cur1 CURSOR READ_ONLY
FOR
        Select  DropStmt
        FROM    @dropAndCreateConstraintsTable
OPEN Cur1
FETCH NEXT FROM Cur1 INTO @DropStatement
WHILE @@FETCH_STATUS = 0
      BEGIN
            PRINT 'Executing ' + @DropStatement
            EXECUTE sp_executesql @DropStatement
            FETCH NEXT FROM Cur1 INTO @DropStatement
      END
CLOSE Cur1
DEALLOCATE Cur1
/* Truncate all tables in the database in the dbo schema */
DECLARE @DeleteTableStatement NVARCHAR(MAX)
DECLARE Cur2 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 Cur2
FETCH NEXT FROM Cur2 INTO @DeleteTableStatement
WHILE @@FETCH_STATUS = 0
      BEGIN
            PRINT 'Executing ' + @DeleteTableStatement
            EXECUTE sp_executesql @DeleteTableStatement
            FETCH NEXT FROM Cur2 INTO @DeleteTableStatement
      END
CLOSE Cur2
DEALLOCATE Cur2
/* Recreate foreign key constraints  */
DECLARE Cur3 CURSOR READ_ONLY
FOR
        Select  CreateStmt
        FROM    @dropAndCreateConstraintsTable
OPEN Cur3
FETCH NEXT FROM Cur3 INTO @RecreateStatement
WHILE @@FETCH_STATUS = 0
      BEGIN
            PRINT 'Executing ' + @RecreateStatement
            EXECUTE sp_executesql @RecreateStatement
            FETCH NEXT FROM Cur3 INTO @RecreateStatement
      END
CLOSE Cur3
DEALLOCATE Cur3
GO

当上面脚本执行成功后,所有表的数据将被清空,约束条件仍然存在。这样就解决了上面提到的问题。


原文(英文)地址:http://www.mssqltips.com/sqlservertip/3218/truncate-all-tables-in-a-sql-server-database/



除非特别声明,本站所有PHP教程及其他教程/文章均为原创、翻译或网友投稿,版权均归UncleToo中文网所有, 转载请注明作者及出处。
原文网址:http://www.uncletoo.com/html/sqlserver/936.html
读完这篇文章后,你是否有所收获? 分享是一种生活的信念!
  • 0
  • 0
我来说两句
更多>>网友评论