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

First, find the name of the table to be processed or the name of the stored procedure in the system table, and process it with a cursor.

PS: sqlserver 2000 uses the system table sysobjects and the type field is xtype. The system table of SQL Server 2005 or later is sys. Objects and the type field is type.

This article uses sql2005 as an example. Please replace SQL2000 according to the above instructions.

Note that different delete commands have different types in SYS. Objects.

For example, if you delete the stored procedure, use drop procedure procedurename to delete the table, and use drop table tablename to delete the trigger, use drop trigger triggername.

The meanings of SYS. Objects. type are as follows:
C: Check constraints.
D: Default Constraint
F: foreign key constraint
L: Log
P: Stored Procedure
PK: primary key constraint
RF: copying and filtering stored procedures
S: system table
TR: trigger
U: used for tables.
Uq: unique constraints.

Batch ProcessingCodeAs follows:
Declare cursorname cursor for select 'drop procedure '+ name from SYS. objects where name like 'xx %' and xtype = 'P' -- delete the corresponding Stored Procedure
Declare cursorname cursor for select 'drop trigger' + name from SYS. objects where name like 'xx % 'and xtype = 'tr' -- delete the corresponding trigger

Open cursorname
Declare @ curname sysname
Fetch next from cursorname into @ curname
While (@ fetch_status = 0)
Begin
Exec (@ curname)
Fetch next from cursorname into @ curname
End
Close cursorname
Deallocate cursorname