SQL SERVER 怎样清空表的数据

于 2013-07-31 12:04:59 发布
阅读量1.1w 收藏 2
点赞数 1
分类专栏： MS SQL Server 数据库维护 数据库资料
本文探讨了 SQL Server 中如何清空表数据并处理外键依赖，包括禁用外键约束的方法和使用 SQL 语句清除表数据的具体步骤。重点介绍了三种清空表数据的需求场景和相应的 SQL 解决方案。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

 SQL SERVER  truncate table tb_name 

   清空表示 怎样屏蔽掉主外键依赖啊

方法1：

--禁用外键约束 
exec   sp_msforeachtable   'alter   table   ?   nocheck   constraint   all ' 

--清空数据 
truncate   table   表名 

--启用外键约束 
exec   sp_msforeachtable   'alter   table   ?   check   constraint   all '

方法2： 

禁用外键
我不建议从关系数据库中删除外键约束。但是，有时（例如在一系列表上执行大量的插入和更新操作以及需要更准确的结果和更佳的性能）您可能需要临时减少对一个或多个外键的引用完整性检查。当然，您只能在以正常的关系顺序无法对整个数据库执行大批量的数据更新时使用此方法。
因此何时才应禁用外键约束呢？假设您的关系数据结构有许多表，所有表都通过外键约束以某种方式彼此相关。与此数据库交互的应用程序具有一个脱机/移动版本，该版本与可能驻留在便携式计算机上的数据库的第二个实例进行通信。对主数据库所做的数据更改可能需要与脱机/移动数据库同步，而同步数据的方法有好几种。
同步数据的一个方法是将插入、更新和删除操作应用于脱机/移动数据库，以便与关系结构相一致。例如，在客户相应的订单前面插入客户，并在相应的订单详细信息前面插入订单。记录删除将按相反的方向进行（从子记录到父记录）。但是，在大型数据库结构上应用此方法时，可能因为太复杂而无法实现和维护。
另一个方法是删除外键约束，之后同步数据，然后重新创建外键约束。此方法只需进行比较小的改动（只需禁用外键约束）即可正常运行。禁用外键约束之后，可以同步数据，然后可以再次启用外键。禁用外键的语法如下所示： 复制代码 
-- Disable foreign key constraint
ALTER TABLE Orders
    NOCHECK CONSTRAINT 
        FK_Orders_Customers

-- Add a new Order record for a non-existent customer
INSERT INTO Orders (CustomerID) VALUES ('BLAH')

-- Select all orders for the non-existent customer
SELECT * FROM Orders WHERE CustomerID = 'BLAH'

此外键强制“订单”表中的 CustomerID 必须是“客户”表中的有效 CustomerID。代码会禁用外键然后将订单插入到“订单”表。插入的订单记录的 CustomerID 在父“客户”表中不存在。由于外键已禁用，完整性检查会被忽略，订单记录将成功插入。
以下代码显示了重新启用外键约束然后测试外键约束工作是否正常的方法。执行此代码时，由于强制约束，订单并未被插入。系统将返回错误消息，表明插入语句与外键约束出现冲突。 复制代码 
-- Enable foreign key constraint
ALTER TABLE Orders
    CHECK CONSTRAINT 
        FK_Orders_Customers

-- Add a new Order record for a non-existent customer
INSERT INTO Orders (CustomerID) VALUES ('BLEH')

在禁用外键、触发器和其他约束时，一定要确保在该时间段内不会在数据库上执行任何数据操作语言 (DML) 操作。这必须在手动执行禁用选项时进行处理。如果您使用 SQL Server 复制和 NOT FOR REPLICATION 语句同步数据，将会自动处理该条件。

方法3： 

MSSQL中如何用SQL清除所有表的数据？这个需求分三种类型： 
第一：只要数据库中表是空的； 
第二：表是空的，并且自增长列可以从１开始增长。 
第三：表是空的，并且自增长列可以从１开始增长，而且存在表间的约束。 
邀月稍微整理了下，放在这里，便于有需要的朋友参阅。 
其实，这不算什么需求。只要用数据库的生成脚本，几分钟即可生成一个干净的表结构及存储过程、视图、约束等。这里提供了另一种用SQL解决问题的方案。权当是无聊的学习，加深点印象吧。呵呵。 
首先，作一些假设：假设database名为TestDB_2000_2005_2008 
预先准备一些脚本 


Sql代码 
use master      
go      
IF OBJECT_ID('TestDB_2000_2005_2008') IS NOT NULL      
-- print 'Exist databse!'    
-- else print 'OK!'    
DROP Database TestDB_2000_2005_2008      
GO      
Create database TestDB_2000_2005_2008      
go      
use TestDB_2000_2005_2008      
go      
IF OBJECT_ID('b') IS NOT NULL      
drop table b      
go      
create table b(id int identity(1,1),ba int,bb int)      
--truncate table b      
insert into b      
select  1,1 union all      
select 2,2 union all      
select 1,1      
IF OBJECT_ID('c') IS NOT NULL      
drop table c      
go      
create table c(id int identity(1,1),ca int,cb int)      
insert into c      
select  1,2 union all      
select 1,3    

use master  
go  
IF OBJECT_ID('TestDB_2000_2005_2008') IS NOT NULL  
-- print 'Exist databse!'  
-- else print 'OK!'  
DROP Database TestDB_2000_2005_2008  
GO  
Create database TestDB_2000_2005_2008  
go  
use TestDB_2000_2005_2008  
go  
IF OBJECT_ID('b') IS NOT NULL  
drop table b    
go  
create table b(id int identity(1,1),ba int,bb int)  
--truncate table b    
insert into b    
select  1,1 union all  
select 2,2 union all  
select 1,1  
IF OBJECT_ID('c') IS NOT NULL  
drop table c  
go  
create table c(id int identity(1,1),ca int,cb int)  
insert into c    
select  1,2 union all  
select 1,3  

先来看看第一种需求： 只要数据库中表是空的。 
这个其实并不难，用一个游标循环得出所有表名，再清除所有表，delete或truncate table 
提供几个语句：以下语句均在SQL2000/SQL2005/SQL2008下使用通过。 

方法甲： 
Sql代码 
use TestDB_2000_2005_2008      
go      
select * from b      
select * from c      
Declare @t varchar (1024)      
Declare @SQL varchar(2048)      
Declare tbl_cur cursor for  select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'    
OPEN tbl_cur FETCH NEXT  from tbl_cur INTO @t    
WHILE @@FETCH_STATUS = 0      
BEGIN    
SET @SQL='TRUNCATE TABLE '+ @t    
--print (@SQL)      
EXEC (@SQL)      
FETCH NEXT  from tbl_cur INTO @t    
END    
CLOSE tbl_cur      
DEALLOCATE tbl_Cur      
select * from b      
select * from c    

use TestDB_2000_2005_2008  
go  
select * from b    
select * from c    
Declare @t varchar (1024)  
Declare @SQL varchar(2048)  
Declare tbl_cur cursor for  select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'  
OPEN tbl_cur FETCH NEXT  from tbl_cur INTO @t  
WHILE @@FETCH_STATUS = 0  
BEGIN  
SET @SQL='TRUNCATE TABLE '+ @t  
--print (@SQL)  
EXEC (@SQL)  
FETCH NEXT  from tbl_cur INTO @t  
END  
CLOSE tbl_cur  
DEALLOCATE tbl_Cur  
select * from b    
select * from c  

方法乙： 
Sql代码 
use TestDB_2000_2005_2008      
go      
select * from b      
select * from c      
select * from d      
select * from e      
DECLARE @TableName VARCHAR(256)      
DECLARE @varSQL VARCHAR(512)      
DECLARE @getTBName CURSOR SET @getTBName = CURSOR FOR SELECT name FROM sys.Tables WHERE NAME NOT LIKE 'Category'    
OPEN @getTBName FETCH NEXT FROM @getTBName INTO @TableName      
WHILE @@FETCH_STATUS = 0      
BEGIN      
SET @varSQL = 'Truncate table '+ @TableName      
--PRINT (@varSQL)      
EXEC (@varSQL)      
FETCH NEXT FROM @getTBName INTO @TableName      
END      
CLOSE @getTBName      
DEALLOCATE @getTBName      
----select * from b      
----select * from c  

use TestDB_2000_2005_2008  
go  
select * from b    
select * from c    
select * from d    
select * from e    
DECLARE @TableName VARCHAR(256)  
DECLARE @varSQL VARCHAR(512)  
DECLARE @getTBName CURSOR SET @getTBName = CURSOR FOR SELECT name FROM sys.Tables WHERE NAME NOT LIKE 'Category'  
OPEN @getTBName FETCH NEXT FROM @getTBName INTO @TableName  
WHILE @@FETCH_STATUS = 0  
BEGIN  
SET @varSQL = 'Truncate table '+ @TableName    
--PRINT (@varSQL)  
EXEC (@varSQL)  
FETCH NEXT FROM @getTBName INTO @TableName  
END  
CLOSE @getTBName  
DEALLOCATE @getTBName  
----select * from b    
----select * from c 

方法丙： 
Sql代码 
Declare @t table(query varchar(2000),tables varchar(100))      
Insert into @t    
    select 'Truncate table ['+T.table_name+']', T.Table_Name from INFORMATION_SCHEMA.TABLES T      
    left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC      
    on T.table_name=TC.table_name      
    where (TC.constraint_Type ='Foreign Key' or TC.constraint_Type is NULL) and    
    T.table_name not in ('dtproperties','sysconstraints','syssegments') and    
    Table_type='BASE TABLE'    
Insert into @t    
    select 'delete from ['+T.table_name+']', T.Table_Name from INFORMATION_SCHEMA.TABLES T      
        left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC      
      on T.table_name=TC.table_name where TC.constraint_Type ='Primary Key' and T.table_name <>'dtproperties'and Table_type='BASE TABLE'    
Declare @sql varchar(8000)      
Select @sql=IsNull(@sql+' ','')+ query from @t    
print(@sql)      
Exec(@sql)    

Declare @t table(query varchar(2000),tables varchar(100))  
Insert into @t  
    select 'Truncate table ['+T.table_name+']', T.Table_Name from INFORMATION_SCHEMA.TABLES T  
    left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC  
    on T.table_name=TC.table_name  
    where (TC.constraint_Type ='Foreign Key' or TC.constraint_Type is NULL) and  
    T.table_name not in ('dtproperties','sysconstraints','syssegments') and  
    Table_type='BASE TABLE'  
Insert into @t  
    select 'delete from ['+T.table_name+']', T.Table_Name from INFORMATION_SCHEMA.TABLES T  
        left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC  
      on T.table_name=TC.table_name where TC.constraint_Type ='Primary Key' and T.table_name <>'dtproperties'and Table_type='BASE TABLE'  
Declare @sql varchar(8000)  
Select @sql=IsNull(@sql+' ','')+ query from @t  
print(@sql)  
Exec(@sql)  


再来看看第二种需求： 表是空的，并且自增长列可以从１开始增长 。 
这种需求其实和第一种差不多。 因为我们在以上语句中使用的是 truncate　table 语句，所以，表的自增长 列是默认从头重新的。 

关键是第三种需求： 表是空的，并且自增长列可以从１开始增长 ，而且存在表间的约束 。 
这是个比较头痛的问题。因为外键约束，不能使用truncate table语句，但是，如果使用delete，又不能使自增长列从１开始重排。 

我们不妨先来增加一些约束条件： 
Sql代码 
CREATE TABLE [d] (  
    [id] [int] IDENTITY (1, 1) NOT NULL ,  
    [da] [int] NULL ,  
    [db] [int] NULL ,  
    CONSTRAINT [PK_d] PRIMARY KEY  CLUSTERED    
    (  
        [id]  
    )  ON [PRIMARY]    
) ON [PRIMARY]  
CREATE TABLE [e] (  
    [id] [int] IDENTITY (1, 1) NOT NULL ,  
    [da] [int] NULL ,  
    [db] [int] NULL ,  
    [did] [int] NULL ,  
    CONSTRAINT [FK_e_d] FOREIGN KEY    
    (  
        [did]  
    ) REFERENCES [d] (  
        [id]  
    )  
) ON [PRIMARY]  
insert into d  
select 5,6 union all  
select 7,8 union all  
select 9,9  
insert into e  
select 8,6,1 union all  
select 8,8,2 union all  
select 8,9,2  

CREATE TABLE [d] ( 
    [id] [int] IDENTITY (1, 1) NOT NULL , 
    [da] [int] NULL , 
    [db] [int] NULL , 
    CONSTRAINT [PK_d] PRIMARY KEY  CLUSTERED 
    ( 
        [id] 
    )  ON [PRIMARY] 
) ON [PRIMARY] 
CREATE TABLE [e] ( 
    [id] [int] IDENTITY (1, 1) NOT NULL , 
    [da] [int] NULL , 
    [db] [int] NULL , 
    [did] [int] NULL , 
    CONSTRAINT [FK_e_d] FOREIGN KEY 
    ( 
        [did] 
    ) REFERENCES [d] ( 
        [id] 
    ) 
) ON [PRIMARY] 
insert into d 
select 5,6 union all 
select 7,8 union all 
select 9,9 
insert into e 
select 8,6,1 union all 
select 8,8,2 union all 
select 8,9,2 

此时再来执行甲乙丙语句时会提示：“无法截断表 'd'，因为该表正由 FOREIGN KEY 约束引用。” 
我们可以这样设想： 
1、先找出没有外键约束的表，truncate 
２、有外键的表，先delete,再复位identity列 
于是得出， 
语句丁（注意没有使用游标 ） 
Sql代码 
SET NoCount ON  
   DECLARE @tableName varchar(512)   
   Declare @SQL varchar(2048)   
   SET @tableName=''  
   WHILE EXISTS   
   (      
   --Find all child tables and those which have no relations   
   SELECT T.table_name   FROM INFORMATION_SCHEMA.TABLES T   
          LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC    ON T.table_name = TC.table_name   
     WHERE ( TC.constraint_Type = 'Foreign Key' OR TC.constraint_Type IS NULL )   
         AND T.table_name NOT IN ( 'dtproperties', 'sysconstraints', 'syssegments' )   
         AND Table_type = 'BASE TABLE'  
         AND T.table_name > @TableName   
         )   
    Begin  
        SELECT @tableName = min(T.table_name)    FROM INFORMATION_SCHEMA.TABLES T   
        LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC    ON T.table_name=TC.table_name   
           WHERE ( TC.constraint_Type = 'Foreign Key' OR TC.constraint_Type IS NULL )   
         AND T.table_name NOT IN ( 'dtproperties', 'sysconstraints', 'syssegments' )   
         AND Table_type = 'BASE TABLE'  
         AND T.table_name > @TableName   
         --Truncate the table   
         SET @SQL = 'Truncate table '+ @TableName    
         print (@SQL)   
         Exec(@SQL)   
     End  
     
   SET @TableName=''  
   WHILE EXISTS   
   (    
   --Find all Parent tables   
     SELECT T.table_name     FROM INFORMATION_SCHEMA.TABLES T   
     LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC     ON T.table_name = TC.table_name   
     WHERE TC.constraint_Type = 'Primary Key'  
     AND T.table_name <> 'dtproperties'  
     AND Table_type='BASE TABLE'  
     AND T.table_name > @TableName   
     )   
   Begin  
     SELECT @tableName = min(T.table_name)   FROM INFORMATION_SCHEMA.TABLES T   
          LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC   ON T.table_name=TC.table_name   
     WHERE TC.constraint_Type = 'Primary Key'  
     AND T.table_name <> 'dtproperties'  
     AND Table_type = 'BASE TABLE'  
     AND T.table_name > @TableName   
     --Delete the table   
       
        SET @SQL = ' delete from '+ @TableName    
         print (@SQL)   
         Exec(@SQL)   
     --Reset identity column   
         IF EXISTS ( SELECT *   FROM INFORMATION_SCHEMA.COLUMNS   
             WHERE COLUMNPROPERTY(   
             OBJECT_ID( QUOTENAME(table_schema)+ '.' + QUOTENAME(@tableName) ),   
             column_name,'IsIdentity'  
             ) = 1   
           )   
     DBCC CHECKIDENT(@tableName,RESEED,0)   
   End  
   SET NoCount OFF  

SET NoCount ON
   DECLARE @tableName varchar(512)
   Declare @SQL varchar(2048)
   SET @tableName=''
   WHILE EXISTS
   (   
   --Find all child tables and those which have no relations
   SELECT T.table_name   FROM INFORMATION_SCHEMA.TABLES T
          LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC    ON T.table_name = TC.table_name
     WHERE ( TC.constraint_Type = 'Foreign Key' OR TC.constraint_Type IS NULL )
         AND T.table_name NOT IN ( 'dtproperties', 'sysconstraints', 'syssegments' )
         AND Table_type = 'BASE TABLE'
         AND T.table_name > @TableName
         )
    Begin
        SELECT @tableName = min(T.table_name)    FROM INFORMATION_SCHEMA.TABLES T
        LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC    ON T.table_name=TC.table_name
           WHERE ( TC.constraint_Type = 'Foreign Key' OR TC.constraint_Type IS NULL )
         AND T.table_name NOT IN ( 'dtproperties', 'sysconstraints', 'syssegments' )
         AND Table_type = 'BASE TABLE'
         AND T.table_name > @TableName
         --Truncate the table
         SET @SQL = 'Truncate table '+ @TableName 
         print (@SQL)
         Exec(@SQL)
     End
  
   SET @TableName=''
   WHILE EXISTS
   ( 
   --Find all Parent tables
     SELECT T.table_name     FROM INFORMATION_SCHEMA.TABLES T
     LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC     ON T.table_name = TC.table_name
     WHERE TC.constraint_Type = 'Primary Key'
     AND T.table_name <> 'dtproperties'
     AND Table_type='BASE TABLE'
     AND T.table_name > @TableName
     )
   Begin
     SELECT @tableName = min(T.table_name)   FROM INFORMATION_SCHEMA.TABLES T
          LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC   ON T.table_name=TC.table_name
     WHERE TC.constraint_Type = 'Primary Key'
     AND T.table_name <> 'dtproperties'
     AND Table_type = 'BASE TABLE'
     AND T.table_name > @TableName
     --Delete the table
    
        SET @SQL = ' delete from '+ @TableName 
         print (@SQL)
         Exec(@SQL)
     --Reset identity column
         IF EXISTS ( SELECT *   FROM INFORMATION_SCHEMA.COLUMNS
             WHERE COLUMNPROPERTY(
             OBJECT_ID( QUOTENAME(table_schema)+ '.' + QUOTENAME(@tableName) ),
             column_name,'IsIdentity'
             ) = 1
           )
     DBCC CHECKIDENT(@tableName,RESEED,0)
   End
   SET NoCount OFF

小结：除了以上方法，还可以临时禁用外键约束。语句为： 
Sql代码 
-- --禁用所有约束   
--exec sp_msforeachtable 'alter table ? nocheck CONSTRAINT all'   
-- --再启用所有外键约束   
--exec sp_msforeachtable 'alter table ? check constraint all' 

如果没有上面的这些约束 还可以这样

declare @sql  varchar(5000)
set @sql = ''
select @sql = @sql +'truncate table '+[name]+';'  from sysobjects where xtype='u'
print  @sql
exec(@sql)


http://bbs.youkuaiyun.com/topics/370076445

注意以上方法还未测试

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值