首页 > 数据库技术 > 详细

SQLSERVER删除所有的表、存储过程和视图

时间:2020-06-27 20:05:28      阅读:103      评论:0      收藏:0      [点我收藏+]
--删除所有约束
DECLARE c1 cursor for
selectalter 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
--删除数据库所有表
declare @tname varchar(8000)
set @tname=‘‘
select @tname=@tname+Name+,from sysobjects where xtype=U
select @tname=drop table + left(@tname,len(@tname)-1)
exec(@tname)

--删除视图:

declare v_mycur cursor local for select [name] from dbo.sysobjects where xtype=V  --声明游标
declare @vname varchar(100) 
  
OPEN v_mycur    --打开游标
  
FETCH NEXT from v_mycur into @vname
  
WHILE @@FETCH_STATUS = 0  
  
BEGIN 
exec(drop VIEW  + @vname) 
FETCH NEXT from v_mycur into @vname   --逐条读取
END 
  
CLOSE v_mycur   --关闭游标

 

--删除存储过程:



declare mycur cursor local for select [name] from dbo.sysobjects where xtype=P
declare @name varchar(100) 
  
OPEN mycur 
  
FETCH NEXT from mycur into @name
  
WHILE @@FETCH_STATUS = 0  
  
BEGIN 
exec(drop PROCEDURE  + @name) 
FETCH NEXT from mycur into @name
END 
  
CLOSE mycur

 

SQLSERVER删除所有的表、存储过程和视图

原文:https://www.cnblogs.com/superfeeling/p/13199461.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!