SQL statement for batch data deletion and batch database table Deletion

We have introduced two methods for batch data deletion (cursor form and SQL stored procedure), as well as the methods for batch data deletion of database tables. For more information, see.

We have introduced two methods for batch data deletion (cursor form and SQL stored procedure), as well as the methods for batch data deletion of database tables. For more information, see.

Cursor deletion method

The Code is as follows:

// Define the cursor DECLARE tables_cursor CURSOR FOR SELECT name FROM sysobjects WHERE type = 'U' // SELECT the user table name OPEN tables_cursor // OPEN the cursor connection DECLARE @ tablename sysname // define the variable Fetch next from tables_cursor INTO @ tablename // read the table name FROM one row in the result set WHILE (@ FETCH_STATUS <>-1) // judge the cursor status BEGIN EXEC ('trunecate table' + @ tablename) // clear the data in the TABLE Fetch next from tables_cursor INTO @ tablename // The NEXT row of data END DEALLOCATE tables_cursor // close the cursor

/42850.htm target = _ blank> SQL stored procedure instance location

The Code is as follows:

Create PROCEDURE Batch_Delete @ TableName nvarchar (100), -- table name @ FieldName nvarchar (100), -- delete the field name @ DelCharIndexID nvarchar (1000) As DECLARE @ PointerPrev int DECLARE @ PointerCurr int DECLARE @ TId NVARCHAR (50), @ SQL NVARCHAR (1000) Set @ PointerPrev = 1 While (@ PointerPrev <LEN (@ DelCharIndexID )) Begin Set @ PointerCurr = CharIndex (',', @ DelCharIndexID, @ PointerPrev) If (@ PointerCurr> 0) Begin SET @ TId = cast (SUBSTRING (@ DelCharIndexID, @ PointerPrev, @ PointerCurr-@ PointerPrev) As NVARCHAR (50 )) SET @ SQL = 'delete from' + @ TableName + 'where' + @ FieldName + '= ''' + @ TID + '''' Exec (@ SQL) Print ('========' + @ TId +' ====== SQL '+ @ SQL) SET @ PointerPrev = @ PointerCurr + 1 Print (@ PointerPrev) End Else Begin Print ('Break ') Break End End -- Delete the last one. Because there is no comma after the last one, it jumps out of the loop and needs to be deleted again. SET @ TId = cast (SUBSTRING (@ DelCharIndexID, @ PointerPrev, LEN (@ DelCharIndexID)-@ PointerPrev + 1) As NVARCHAR (50 )) SET @ SQL = 'delete from' + @ TableName + 'where' + @ FieldName + '= ''' + @ TID + '''' Exec (@ SQL) Print ('========' + @ TId +' ====== SQL '+ @ SQL) GO

Batch table deletion methods found online

The Code is as follows:

/*-------------------------------- Function Description: Batch DropTable Usage instructions: Be careful when using it, because the where condition for deleting the selected table is like. All where conditions must be guaranteed. The like parameter matches the name of the table to be deleted. ---------------------------------*/ -------- Parameter definition ------------------- DECLARE @ tableName AS Nvarchar (50) -- query the table name conditions (Be careful !, Make sure that the like condition is the table you want to Drop. TableName is as accurate as possible) SET @ tableName = 'test' -------------------------------------- -- SELECT name FROM sys. tables WHERE name LIKE '%' + @ tableName + '%' -- query the name of the table to be deleted. IF @ tableName = ''set @ tableName = 'tablename' -- initialize tableName to TableName to prevent @ tableName from being empty DECLARE @ tableNames AS Nvarchar (3000) DECLARE @ SQL AS Nvarchar (3000) SET @ tableNames = (SELECT ',' + name FROM sys. tables WHERE name LIKE '%' + @ tableName + '%' for xml path ('')) SET @ tableNames = Stuff (@ tableNames, 1, 1 ,'') SET @ SQL = 'drop table' + @ tableNames EXEC (@ SQL)