请教：如何清除指定数据库中所有表的记录？

[jwdream2008]

就是要一个空的数据库？

[忆轩辕]

[Quote=引用 9 楼 jwdream2008 的回复:]
引用 8 楼 luoyoumou 的回复:
----新创建一个空数据库不就得了？
----犯得着这样麻烦吗？

那数据库里面的表，我还要一个一个得建立，多麻烦？？
[/Quote]

平时不保存建库脚本的？

[--小F--]

truncate table

[华夏小卒]

好像有个系统过程，加2个变量，和命令，就可以了

[fwacky]

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

[jiangshun]

导出脚本，删除数据库，然后重新运行脚本

[fwacky]

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

[--小F--]

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 约束引用。”

[--小F--]

我们可以这样设想： 
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'  

[luoyoumou]

----新创建一个空数据库不就得了？
----犯得着这样麻烦吗？

[jwdream2008]

[Quote=引用 8 楼 luoyoumou 的回复:]
----新创建一个空数据库不就得了？
----犯得着这样麻烦吗？
[/Quote]
那数据库里面的表，我还要一个一个得建立，多麻烦？？

[jiangjubo]

[Quote=引用 5 楼 fwacky 的回复:]
SQL codedeclare@sqlvarchar(5000)set@sql=''select@sql=@sql+'truncate table'+[name]+';'from sysobjectswhere xtype='u'print@sqlexec(@sql)
[/Quote] 哈哈,我就是用这个方法的