——————————-清空表資料———

 

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)