删除sql对象

 

 

 create   proc   usp_deny   (@userid   varchar(20))
as
declare   @sql   varchar(1000)
declare   @name   varchar(100)
declare   @type   varchar(10)
if   @userid   is   null  
return
declare   cur_objects   cursor   for
select   name,type   from   sysobjects   where   xtype   in   ( 'P ', 'V ', 'FN ', 'TF ')
open   cur_objects
fetch   next   from   cur_objects   into   @name,@type
while   @@fetch_status=0
begin
if   @type   =   'V ' --视图
begin
select   @sql   =   'DROP   VIEW   '+@name
exec(@sql)
end
if   @type   =   'P ' --存储过程
begin
select   @sql   =   'DROP   PROC   '+@name
exec(@sql)
end
if   @type   in( 'FN ', 'TF ') --函数
begin
select   @sql   =   'DROP   FUNCTION   '+@name
exec(@sql)
end
fetch   next   from   cur_objects   into   @name,@type
end
close   cur_objects
deallocate   cur_objects

 

--

欢迎访问我的博客http://lxingxy.blog.163.com/