Bulk Delete SQL Server objects (tables, stored procedures, triggers)

Find the table name or the name of the stored procedure in the system table before processing it with a cursor

Ps:sqlserver 2000 is using the system table is sysobjects, the Type field is: xtype; The system table for SQL Server 2005 or later is sys.objects, and the Type field is type

In this article, for example, Sql2005, Sql2000 version, please follow the above instructions to replace

Note the value of type in sys.objects is different from the delete command

Delete a trigger with DROP table tablename Drop Trigger triggername If you delete a stored procedure by dropping PROCEDURE procedurename

The value of the sys.objects.type is expressed in the following table:
C: Check the constraint.
D: Default constraint
F: FOREIGN KEY constraint
L: Log
P: Stored Procedure
PK: PRIMARY KEY constraint
RF: Replication Filtering stored Procedures
S: System table
TR: Trigger
U: For tables.
UQ: a unique constraint.

The code for batch processing is as follows:
DECLARE cursorname cursor FOR select ' Drop PROCEDURE ' +name from sys.objects where name like ' xx% ' and xtype = ' P '--delete corresponding The stored procedures
DECLARE cursorname cursor FOR select ' Drop Trigger ' +name from sys.objects where name like ' xx% ' and xtype = ' TR '--delete corresponding trigger Manager

Open Cursorname
declare @curname sysname
FETCH NEXT from Cursorname to @curname
while (@ @fetch_status =0)
Begin
EXEC (@curname)
FETCH NEXT from Cursorname to @curname
End
Close Cursorname
Deallocate Cursorname

Bulk Delete SQL Server objects (tables, stored procedures, triggers)