删除数据库所有存储过程的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步**********删除所有表*************************/

  
  

   
   use
   
    数据库

   
   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
   
   )
  
  

--/第2步**********删除所有存储过程*************************/

  
  

   
   use
   
    数据库

   
   declare
   
    
   
   @tname
   
    
   
   varchar
   
   (
   
   8000
   
   )

   
   set
   
    
   
   @tname
   
   =
   
   ''
   
   

   
   select
   
    
   
   @tname
   
   =
   
   @tname
   
    
   
   +
   
    Name 
   
   +
   
    
   
   '
   
   ,
   
   '
   
    
   
   from
   
    sysobjects 
   
   where
   
    xtype
   
   =
   
   '
   
   P
   
   '
   
   

   
   select
   
    
   
   @tname
   
   =
   
   '
   
   drop Procedure 
   
   '
   
    
   
   +
   
    
   
   left
   
   (
   
   @tname
   
   ,
   
   len
   
   (
   
   @tname
   
   )
   
   -
   
   1
   
   )

   
   exec
   
   (
   
   @tname
   
   )