删除数据库所有存储过程的SQL语句

下面为您介绍能够一次性删除数据库所有存储过程的SQL语句，供您参考，如果您对相关的SQL语句感兴趣，不妨一看，希望能够对您有所启迪。

–/第1步**********删除所有表的外键约束*************************/

[[15062]][[15063]]代码

    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
   )