--方法-:
-------------清空数据库-------------
/*
描述:
清空数据库
步骤:
清空外键表
清空对应外键表已经清空了的主表
版本:
时间 修改人 操作
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