由于一些外键的约束关系,使全部表数据的删除变的很困难,通常需要执行多次的批量删除才可以完全清除.这里的存储过程考虑到了外键的约束关系,使表数据按照外键约束的先后顺序来删除表内容.一次执行即可删除全部数据

create proc sp_detall
as
begin tran
declare @id int
declare @sql varchar(200)
declare @num int,@numed int
declare @deted table(id int not null)
declare @notdeted table(id int not null)
insert into @notdeted select id from   sysobjects where xtype='u'

--这里可以加一些限制条件,保留一些表的数据,但要注意约束关系

insert into @deted
  select id from @notdeted where  id not in (select distinct rkeyid from sysforeignkeys)
declare mycursor cursor for select id from @deted
open mycursor
fetch next from mycursor into @id
while(@@fetch_status=0)
begin
 select @sql='delete from '+name from sysobjects where  id=@id
 exec(@sql)
 fetch next from mycursor into @id
end
close mycursor
deallocate mycursor

select @num=count(1) from  @notdeted
select @numed=count(1) from  @deted

while (@numed<@num)
begin
 delete from @notdeted where id in (select id from @deted)
 declare mycursor cursor for
  select id from @notdeted a where (select count(1) from sysforeignkeys where rkeyid=a.id and fkeyid  in (select id from @notdeted))=0
 open mycursor
 fetch next from mycursor into @id
 while(@@fetch_status=0)
 begin
  select @sql='delete from '+name from sysobjects where  id=@id
  exec(@sql)
  insert into @deted select @id
  fetch next from mycursor into @id
 end
 close mycursor
 deallocate mycursor
 select @numed=count(1) from  @deted
end

commit