SQL批量删除外表和表代码,MSSQL代码库,德仔网

代码语言

代码分类

【MSSQL】 SQL批量删除外表和表

作者:Dezai.CN / 发布于2012/11/27/ 628

--批量删除用户表
--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

评论列表

本站所提供的代码，版权归原作者所有，若有侵犯作者版权，请与我们联系，我们将立即删除或修改。谢谢!
本站所有代码发布及提供者。

试试其它关键字

　批量删除　

同语言下

可能有用的

Dezai.CN贡献的其它代码(4037)