SQL wholesale table deletion and stored procedure Deletion

The main idea is as follows:

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.

Note that the values of sysobjects. xtype are different deletion commands. For example, when you delete a stored procedure, you can use drop procedure procedurename to delete a table. The value of drop table tablename sysobjects. xtype indicates the following table:

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 table cells.

Uq: unique constraints.

Batch ProcessingCodeAs follows:

Declare cursorname cursor for select 'drop procedure '+ name from sysobjects where name like 'xx %' and xtype = 'P' -- delete the corresponding Stored Procedure

Declare cursorname cursor for select 'drop table' + name from sysobjects where name like 'xx % 'and xtype = 'U' -- delete the corresponding table

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