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

1. use master   
2. go   
3. IF OBJECT_ID('TestDB_2000_2005_2008') IS NOT NULL   
4. -- print 'Exist databse!'  
5. -- else print 'OK!'  
6. DROP Database TestDB_2000_2005_2008   
7. GO   
8. Create database TestDB_2000_2005_2008   
9. go   
10. use TestDB_2000_2005_2008   
11. go   
12. IF OBJECT_ID('b') IS NOT NULL   
13. drop table b    
14. go   
15. create table b(id int identity(1,1),ba int,bb int)   
16. --truncate table b    
17. insert into b    
18. select  1,1 union all   
19. select 2,2 union all   
20. select 1,1   
21. IF OBJECT_ID('c') IS NOT NULL   
22. drop table c   
23. go   
24. create table c(id int identity(1,1),ca int,cb int)   
25. insert into c    
26. select  1,2 union all   
27. select 1,3  

方法甲：

1. ********************MSSQL 2000/2005/2008***********************/   
2. use TestDB_2000_2005_2008   
3. go   
4. select * from b    
5. select * from c    
6. Declare @t varchar (1024)   
7. Declare @SQL varchar(2048)   
8. Declare tbl_cur cursor for  select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'  
9. OPEN tbl_cur FETCH NEXT  from tbl_cur INTO @t  
10. WHILE @@FETCH_STATUS = 0   
11. BEGIN  
12. SET @SQL='TRUNCATE TABLE '+ @t  
13. --print (@SQL)   
14. EXEC (@SQL)   
15. FETCH NEXT  from tbl_cur INTO @t  
16. END  
17. CLOSE tbl_cur   
18. DEALLOCATE tbl_Cur   
19. select * from b    
20. 方法乙：
1. /********************MSSQL 2000/2005/2008***********************/  
2. use TestDB_2000_2005_2008   
3. go   
4. select * from b    
5. select * from c    
6. select * from d    
7. select * from e    
8. DECLARE @TableName VARCHAR(256)   
9. DECLARE @varSQL VARCHAR(512)   
10. DECLARE @getTBName CURSOR SET @getTBName = CURSOR FOR SELECT name FROM sys.Tables WHERE NAME NOT LIKE 'Category'  
11. OPEN @getTBName FETCH NEXT FROM @getTBName INTO @TableName   
12. WHILE @@FETCH_STATUS = 0   
13. BEGIN   
14. SET @varSQL = 'Truncate table '+ @TableName    
15. --PRINT (@varSQL)   
16. EXEC (@varSQL)   
17. FETCH NEXT FROM @getTBName INTO @TableName   
18. END   
19. CLOSE @getTBName   
20. DEALLOCATE @getTBName   
21. ----select * from b    
22. ----select * from c  
• /********************MSSQL 2000/2005/2008***********************/ 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

   

  
  方法丙：

   

  + expand sourceview plaincopy to clipboardprint?

  1. Declare @t table(query varchar(2000),tables varchar(100))   
  2. Insert into @t  
  3.     select 'Truncate table ['+T.table_name+']', T.Table_Name from INFORMATION_SCHEMA.TABLES T   
  4.     left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC   
  5.     on T.table_name=TC.table_name   
  6.     where (TC.constraint_Type ='Foreign Key' or TC.constraint_Type is NULL) and  
  7.     T.table_name not in ('dtproperties','sysconstraints','syssegments') and  
  8.     Table_type='BASE TABLE'  
  9. Insert into @t  
  10.     select 'delete from ['+T.table_name+']', T.Table_Name from INFORMATION_SCHEMA.TABLES T   
  11.         left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC   
  12.        on T.table_name=TC.table_name where TC.constraint_Type ='Primary Key' and T.table_name <>'dtproperties'and Table_type='BASE TABLE'  
  13. Declare @sql varchar(8000)   
  14. Select @sql=IsNull(@sql+' ','')+ query from @t  
  15. print(@sql)   
  16. 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，又不能使自增长列从１开始重排。
  
  我们不妨先来增加一些约束条件：

   

1. CREATE TABLE [d] (   
2.     [id] [int] IDENTITY (1, 1) NOT NULL ,   
3.     [da] [int] NULL ,   
4.     [db] [int] NULL ,   
5.     CONSTRAINT [PK_d] PRIMARY KEY  CLUSTERED    
6.     (   
7.         [id]   
8.     )  ON [PRIMARY]    
9. ) ON [PRIMARY]   
10. CREATE TABLE [e] (   
11.     [id] [int] IDENTITY (1, 1) NOT NULL ,   
12.     [da] [int] NULL ,   
13.     [db] [int] NULL ,   
14.     [did] [int] NULL ,   
15.     CONSTRAINT [FK_e_d] FOREIGN KEY    
16.     (   
17.         [did]   
18.     ) REFERENCES [d] (   
19.         [id]   
20.     )   
21. ) ON [PRIMARY]   
22. insert into d   
23. select 5,6 union all   
24. select 7,8 union all   
25. select 9,9   
26. insert into e   
27. select 8,6,1 union all   
28. select 8,8,2 union all   
29. select 8,9,2  
30. •  

    此时再来执行甲乙丙语句时会提示：“无法截断表 'd'，因为该表正由 FOREIGN KEY 约束引用。”
    
    我们可以这样设想：
    1、先找出没有外键约束的表，truncate
    ２、有外键的表，先delete,再复位identity列
    于是得出，
    语句丁（注意没有使用游标 ）

31. SET NoCount ON   
32.    DECLARE @tableName varchar(512)   
33.    Declare @SQL varchar(2048)   
34.    SET @tableName=''  
35.    WHILE EXISTS   
36.    (      
37.    --Find all child tables and those which have no relations   
38.    SELECT T.table_name   FROM INFORMATION_SCHEMA.TABLES T   
39.           LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC    ON T.table_name = TC.table_name   
40.      WHERE ( TC.constraint_Type = 'Foreign Key' OR TC.constraint_Type IS NULL )   
41.          AND T.table_name NOT IN ( 'dtproperties', 'sysconstraints', 'syssegments' )   
42.          AND Table_type = 'BASE TABLE'  
43.          AND T.table_name > @TableName   
44.          )   
45.     Begin   
46.         SELECT @tableName = min(T.table_name)    FROM INFORMATION_SCHEMA.TABLES T   
47.         LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC    ON T.table_name=TC.table_name   
48.            WHERE ( TC.constraint_Type = 'Foreign Key' OR TC.constraint_Type IS NULL )   
49.          AND T.table_name NOT IN ( 'dtproperties', 'sysconstraints', 'syssegments' )   
50.          AND Table_type = 'BASE TABLE'  
51.          AND T.table_name > @TableName   
52.          --Truncate the table   
53.          SET @SQL = 'Truncate table '+ @TableName    
54.          print (@SQL)   
55.          Exec(@SQL)   
56.      End   
57.      
58.    SET @TableName=''  
59.    WHILE EXISTS   
60.    (    
61.    --Find all Parent tables   
62.      SELECT T.table_name     FROM INFORMATION_SCHEMA.TABLES T   
63.      LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC     ON T.table_name = TC.table_name   
64.      WHERE TC.constraint_Type = 'Primary Key'  
65.      AND T.table_name <> 'dtproperties'  
66.      AND Table_type='BASE TABLE'  
67.      AND T.table_name > @TableName   
68.      )   
69.    Begin   
70.      SELECT @tableName = min(T.table_name)   FROM INFORMATION_SCHEMA.TABLES T   
71.           LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC   ON T.table_name=TC.table_name   
72.      WHERE TC.constraint_Type = 'Primary Key'  
73.      AND T.table_name <> 'dtproperties'  
74.      AND Table_type = 'BASE TABLE'  
75.      AND T.table_name > @TableName   
76.      --Delete the table   
77.        
78.         SET @SQL = ' delete from '+ @TableName    
79.          print (@SQL)   
80.          Exec(@SQL)   
81.      --Reset identity column   
82.          IF EXISTS ( SELECT *   FROM INFORMATION_SCHEMA.COLUMNS   
83.              WHERE COLUMNPROPERTY(   
84.              OBJECT_ID( QUOTENAME(table_schema)+ '.' + QUOTENAME(@tableName) ),   
85.              column_name,'IsIdentity'  
86.              ) = 1   
87.            )   
88.      DBCC CHECKIDENT(@tableName,RESEED,0)   
89.    End   结：除了以上方法，还可以临时禁用外键约束。语句为：
    
    view plaincopy to clipboardprint?

    1. -- --禁用所有约束   
    2. --exec sp_msforeachtable 'alter table ? nocheck CONSTRAINT all'  
    3. -- --再启用所有外键约束   
    4. --exec sp_msforeachtable 'alter table ? check constraint all'

90.