Howto: Batch delete stored procedures and tables

1. delete a stored procedure Code
Begin   Transaction

Declare Drop_p_cursor Cursor   For
Select   ' Drop procedure '   +   [ Name ]   From Sysobjects Where Xtype =   ' P '   And Category =   0

OpenDrop_p_cursor

Declare   @ SQL Sysname
Fetch   Next   From Drop_p_cursor Into   @ SQL
While ( @ Fetch_status   =   0 )
Begin
Exec ( @ SQL )
Fetch   Next   From Drop_p_cursor Into   @ SQL

2. delete a table
Note: When deleting a table, delete the constraint first. Nocheck does not work in this example. Drop is required.

Code
Begin   Transaction
Declare   @ SQL Sysname

Declare Drop_u_cursor Cursor   For
Select   ' Drop table '   +   [ Name ]   From Sysobjects Where Xtype =   ' U '   And Category =   0
Declare Forbiden_f_cursor Cursor   For
Select   ' ALTER TABLE '   + TBL. Name +   ' Drop Constraint '   + CST. Name
From Sysobjects TBL, sysobjects CST Where CST. xtype =   ' F '   And CST. parent_obj = TBL. ID

Open Forbiden_f_cursor
Fetch   Next   From Forbiden_f_cursor Into   @ SQL
While ( @ Fetch_status   =   0 )
Begin
Print   @ SQL
Exec ( @ SQL )
Fetch   Next   From Forbiden_f_cursor Into   @ SQL
End
Close Forbiden_f_cursor
Deallocate Forbiden_f_cursor

Open Drop_u_cursor
Fetch   Next   From Drop_u_cursor Into   @ SQL
While ( @ Fetch_status   =   0 )
Begin
Print   @ SQL
Exec ( @ SQL )
Fetch   Next   From Drop_u_cursor Into   @ SQL
End

CloseDrop_u_cursor
DeallocateDrop_u_cursor

Go
If   @ Error   <>   0
Begin
Rollback   Transaction
Return
End
Else
  Commit   Transaction