sql语句删除库中所有表
时间：2021-07-01 10:21:17
帮助过：65人阅读
--/第1步**********删除所有表的外键约束*************************/
2 use DCCalc_Engine
3 GO
4 DECLARE c1
cursor for
5 select ‘alter table [‘+ object_name(parent_obj)
+ ‘] drop constraint [‘+name
+‘]; ‘
6 from sysobjects
7 where xtype
= ‘F‘
8 open c1
9 declare @c1 varchar(
8000)
10 fetch next from c1
into @c1
11 while(
@@fetch_status=0)
12 begin
13 exec(
@c1)
14 fetch next from c1
into @c1
15 end
16 close c1
17 deallocate c1
18
19 --/第2步**********删除所有表*************************/
20
21 use DCCalc_Engine
22 GO
23 declare @sql varchar(
8000)
24 while (
select count(
*)
from sysobjects
where type
=‘U‘)
>0
25 begin
26 SELECT @sql=‘drop table ‘ + name
27 FROM sysobjects
28 WHERE (type
= ‘U‘)
29 ORDER BY ‘drop table ‘ + name
30 exec(
@sql)
31 end
32 --/第2步**********删除所有存储过程*************************/
33 use 数据库
34 declare @tname varchar(
8000)
35 set @tname=‘‘
36 select @tname=@tname + Name
+ ‘,‘ from sysobjects
where xtype
=‘P‘
37 select @tname=‘drop Procedure ‘ + left(
@tname,
len(
@tname)
-1)
38 exec(
@tname)
sql语句删除库中所有表
标签：span use while cts alc dealloc oca name ***
