SQL批量删除用户表的方法(1)
发布时间：2010-09-02 10:53 来源：未知
使用SQL语句，如何才能批量删除用户表呢？接下来的SQL server内容就将为您介绍SQL批量删除用户表(先删除所有外键约束,再删除所有表) 的方法，供您参考，希望对您有所帮助。
--1.删除外键约束
使用SQL语句，如何才能批量删除用户表呢？接下来的SQL server内容就将为您介绍SQL批量删除用户表(先删除所有外键约束,再删除所有表) 的方法，供您参考，希望对您有所帮助。
--1.删除外键约束
DECLARE c1 cursor for
select 'alter table ['+ object_name(parent_obj) + '] drop constraint ['+name+']; '
from sysobjects
where xtype = 'F'
open c1
declare @c1 varchar(8000)
fetch next from c1 into @c1
while(@@fetch_status=0)
begin
exec(@c1)
fetch next from c1 into @c1
end
close c1
deallocate c1
--2.删除表
DECLARE c2 cursor for
select 'drop table ['+name +']; '
from sysobjects
where xtype = 'u'
open c2
declare @c2 varchar(8000)
fetch next from c2 into @c2
while(@@fetch_status=0)
begin
exec(@c2)
fetch next from c2 into @c2
end
close c2
deallocate c2
--批量清除表内容：
--1.禁用外键约束
DECLARE c1 cursor for
select 'alter table ['+ object_name(parent_obj) + '] nocheck constraint ['+name+']; '
from sysobjects
where xtype = 'F'
open c1
declare @c1 varchar(8000)
fetch next from c1 into @c1
while(@@fetch_status=0)
begin
exec(@c1)
fetch next from c1 into @c1
end
close c1
deallocate c1
--2.清除表内容
DECLARE c2 cursor for
select 'truncate table ['+name +']; '
from sysobjects
where xtype = 'u'
open c2
declare @c2 varchar(8000)
fetch next from c2 into @c2
while(@@fetch_status=0)
begin
exec(@c2)
fetch next from c2 into @c2
end
close c2
deallocate c2
--3.启用外键约束
DECLARE c1 cursor for
select 'alter table ['+ object_name(parent_obj) + '] check constraint ['+name+']; '
from sysobjects
where xtype = 'F'
open c1
declare @c1 varchar(8000)
fetch next from c1 into @c1
while(@@fetch_status=0)
begin
exec(@c1)
fetch next from c1 into @c1
end
close c1
deallocate c1
