自动清空数据库中的数据

--方法－：

-------------清空数据库-------------

/*
 描述：
  清空数据库
 步骤：
  清空外键表
  清空对应外键表已经清空了的主表
 版本：
  时间   修改人  操作
  2007年04月09日 mengmou 创建
 变量：
  @name  varchar，表名
  @sql  varchar，动态sql语句
 临时表：
  #tmp  存放有主外键对应关系的表名
  #table  存放对应外键表已清空、可以delete的表名
*/

--禁用触发器
exec sp_msforeachtable @command1 = "alter table ? disable trigger all"

go
----清空外键表----

--更正 sysindexes 表的不正确内容
dbcc updateusage (0) with count_rows,no_infomsgs

declare @name sysname,@sql varchar(8000) set @sql = ''

--用游标组装sql语句删除表记录
declare t_cursor cursor for
select a.name 
from sysobjects a join sysindexes b on a.id = b.id 
where a.xtype='u' and b.indid < 2 and b.rows > 0 and a.id not in (select distinct rkeyid from sysreferences)
open t_cursor
fetch next from t_cursor into @name
while @@fetch_status = 0
begin
 if len(@sql) + 10 + len(@name) > 8000
 begin
  exec (@sql)
  set @sql = '' 
 end
 set @sql = @sql + ' delete [' + @name + ']'
 fetch next from t_cursor into @name
end
close t_cursor
deallocate t_cursor

exec (@sql)

dbcc updateusage (0) with count_rows,no_infomsgs

go

----清空主表----

declare @sql varchar(8000) set @sql = ''
create table #table(rkeyid sysname)

select distinct _r.fkeyid,_r.rkeyid into #tmp
from sysreferences _r join sysindexes _i on _i.id = _r.rkeyid
where _i.indid < 2 and _i.rows > 0

while @@rowcount > 0
begin--清空对应外键表已经清空了的主表
 insert #table
 select distinct rkeyid
 from #tmp _t
 where exists (select 1 from sysindexes where id = _t.rkeyid and indid < 2 and rows > 0)
  and not exists (
   select 1 from #tmp _t2 join sysindexes _i on _i.id = _t2.fkeyid
   where _i.indid < 2 and _i.rows > 0 and _t2.rkeyid = _t.rkeyid and _t2.fkeyid <> _t2.rkeyid
  )
 
 --这里@sql长度应该不会大于8000，不必用游标拆分字符串。
 select @sql = @sql + ' delete [' + object_name(rkeyid) + ']' from #table

 exec(@sql)

 set @sql = ''
 
 dbcc updateusage (0) with count_rows,no_infomsgs

 delete #table
end

go

drop table #tmp,#table
go
--起用触发器
exec sp_msforeachtable @command1 = "alter table ? enable trigger all"

go

--查询
create table #table_count(table_name sysname,table_count int)

exec sp_msforeachtable "insert #table_count select '?',count(*) from ?"

select * from #table_count --where table_count > 0 
order by table_count desc,table_name

drop table #table_count

go

----------------------------------------------------------------------------

--方法二:

-------------清空数据库-------------

create proc p_delete_table(@table_id int)
as
/*
 描述：
  如果参数表是外键表，不做任何动作。
  如果参数表是主表，则递归清空它周围的外键表，最后清空主表。
 版本：
  时间   修改人  操作
  2007年05月09日  mengmou  创建
 变量：
  @table_id 表ID
  @table  表变量，存放构成树形结构的表
*/

begin
 dbcc updateusage (0) with count_rows,no_infomsgs
 declare @sql varchar(8000),@level int, @id int
 select @sql = '',@level = 1
 declare @table table (table_id int,level int)
 
 if not exists(select 1 from sysreferences where fkeyid = @table_id and fkeyid <> rkeyid)
  and exists(select 1 from sysindexes where id = @table_id and indid < 2 and rows > 0)
  insert @table select @table_id,@level
 else 
  return 

 while @@rowcount > 0
 begin
  set @level = @level + 1

  insert @table
  select distinct fkeyid,@level
  from @table _t 
  join sysreferences _r  on _r.rkeyid = _t.table_id 
  join sysindexes _i on _i.id = _r.fkeyid 
  where _r.fkeyid <> _r.rkeyid and _t.level = @level - 1 and _i.indid < 2 and _i.rows > 0
 end

 --@sql超过8000要用游标拆分字符串
 declare t_cursor cursor for
 select table_id from @table order by level desc
 open t_cursor
 fetch next from t_cursor into @id
 while @@fetch_status = 0
 begin
  select @sql = 'delete [' + object_name(@id) + ']'
  exec(@sql)
  fetch next from t_cursor into @id
 end
 close t_cursor
 deallocate t_cursor
 
end

go 

--禁用触发器
exec sp_msforeachtable @command1 = "alter table ? disable trigger all"

go

exec sp_msforeachtable @command1 = "declare @id int select @id = object_id('?') exec p_delete_table @id"

go

--起用触发器
exec sp_msforeachtable @command1 = "alter table ? enable trigger all"

go

--查询

create table #table_count(table_name sysname,table_count int)

exec sp_msforeachtable "insert #table_count select '?',count(*) from ?"

select * from #table_count where table_count > 0 order by table_count desc,table_name

drop table #table_count

go

drop proc p_delete_table