--用了游标循环删除，速度有点慢，
--其实可以考虑先生成对应关系的脚本，再删除现有关系、约束等，然后再用TRUNCATE TABLE来删除，
--最后再重建关系会否快一些呢？呵呵，有空测试一下
begin tran
SET NOCOUNT ON
--记录所有外键相关表
select object_name(fkeyID) as cName,
object_name(rkeyID) as fName
into #FkeyTable
from sysforeignkeys
--建立临时表，记录已经删除的表
CREATE table #HaveDel
(
TableName varchar(100)
)
declare @TableName varchar(100)
--循环删除只具有 FOREIGN KEY 约束的表对象
while exists (select cName from #FkeyTable )
begin
select cName
into #NowTable
from
(
select cName
from #FkeyTable
) as a
left join
(
select fName
from #FkeyTable
) as b
on a.cName=b.Fname
where b.fname is null
declare cur_DelTable cursor
for
select cName
from #NowTable
open cur_DelTable
fetch next from cur_DelTable into @TableName
while @@FETCH_STATUS = 0
begin
print '正在删除：'+ @TableName
exec ('delete from ' + @TableName)
insert into #HaveDel select @TableName
fetch next from cur_DelTable into @TableName
end
close cur_DelTable
deallocate cur_DelTable
--删除掉外键相关表中已经删除的表的相关记录
delete
from #FkeyTable
where exists (select cName from #NowTable where #FkeyTable.cName = #NowTable.cname)
drop table #NowTable
end
drop table #FkeyTable
--删除剩余表记录
declare cur_DelTable cursor
for
select tbl.name
from sysobjects as tbl
left join #HaveDel on tbl.name=#HaveDel.TableName
where #HaveDel.TableName is null
and tbl.xtype='u'
open cur_DelTable
fetch next from cur_DelTable into @TableName
while @@FETCH_STATUS=0
begin
print '正在删除：'+ @TableName
exec ('delete from ' + @TableName)
fetch next from cur_DelTable into @TableName
end
close cur_DelTable
deallocate cur_DelTable
drop table #HaveDel
print '删除完成！'
commit tran
