SQL批量删除用户表的方法

数据库 SQL Server
下面就将为您介绍SQL批量删除用户表(先删除所有外键约束,再删除所有表) 的方法,供您参考,希望对您有所帮助。

使用SQL语句,如何才能批量删除用户表呢?下面就将为您介绍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#p#
    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' #p#
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
 

责任编辑:段燃 来源: 互联网
相关推荐

2010-09-03 11:52:41

SQL删除

2010-08-04 09:55:34

LINQ to SQL

2010-09-02 11:34:51

SQL删除

2010-09-01 16:47:18

SQL删除

2010-10-22 16:48:49

SQL删除所有表数据

2010-09-03 11:47:38

SQL删除

2010-09-01 16:26:11

SQL删除批量

2010-11-10 13:42:32

SQL Server删

2010-09-03 11:05:59

SQL删除

2010-09-16 16:23:06

sql server批

2010-09-03 12:01:17

SQL删除

2010-11-10 11:42:35

SQL Server2

2010-09-01 15:43:57

2010-09-09 15:23:16

SQL更新数据

2010-09-08 13:14:57

SQL删除约束

2010-11-10 13:28:06

SQL Server删

2010-11-11 09:20:46

SQL Server创

2010-09-02 10:36:51

SQL删除

2010-09-03 13:30:31

SQL删除

2011-07-11 13:22:28

存储过程
点赞
收藏

51CTO技术栈公众号