SQL Server quickly deletes entire database tables and stored procedures

Situation: The remote database delete table execution is too slow, too many tables, the database does not have permission to delete

Result: Empty database is reserved

Methods: Using SQL statements, query the Network Digest solution.

Description

Some are constrained and cannot be deleted directly, you need to delete all the constraints first, the statement:

DECLARE C1 Cursor for    Select 'ALTER TABLE ['+ object_name (parent_obj) +'] Drop constraint ['+name+']; '     fromsysobjectswhereXtype ='F'Open c1declare @c1 varchar (8000) FETCH Next fromC1 into @c1 while(@ @fetch_status =0) begin EXEC (@c1) fetch next fromC1 into @c1 endclose c1deallocate C1

Select ' TRUNCATE TABLE ' + name + '; ' from sysobjects where xtype= ' U ' ORDER by Name ASC;

After the execution of this statement, all the tables in the database are queried, and the TRUNCATE statement is executed after copying it.

Delete all tables in the database, statements:

Select 'TRUNCATE TABLE'+ Name +';'  fromsysobjectswhereXtype='U'ORDER BY name Asc;declare @tname varchar (8000)Set@tname ="'Select@[email protected] + Name +','  fromsysobjectswhereXtype='U'Select@tname ='drop table'+ Left (@tname, Len (@tname)-1) EXEC (@tname)

If you need to delete a stored procedure, you just need to change the above to make the following changes to the where xtype= ' U ' to where xtype= ' P ', drop table to drop Procedure

Delete all stored procedures for the database, statements:

Select 'truncate Procedure'+ Name +';'  fromsysobjectswhereXtype='P'ORDER BY name Asc;declare @tname varchar (8000)Set@tname ="'Select@[email protected] + Name +','  fromsysobjectswhereXtype='P'Select@tname ='Drop Procedure'+ Left (@tname, Len (@tname)-1) EXEC (@tname)